many rows to one column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jennyrd - 28 Aug 2006 19:18 GMT

I have a view which selects these columns: employeenumber,ClientID,Billing
code,amountBilled,InvoiceNumber from a number of other tables.

One Employee may have many billingAmounts.
My view right now has each row for every Amount Billed for each Employee.I
want to have a query which has only one row for each employee with repeating
cloumns if they have multiple billings.

If an employee A has one row in the view,i want my output in this format.
EmployeeNumber,ClientID,BillingCode1,AmountBilled1 ,InvoiceNumber1

If an employee A has two rows in the View,i want my output in this format.
EmployeeNumber,ClientID,BillingCode1,AmountBilled1
,InvoiceNumber1,BillingCode2,AmountBilled2,Invoice Number2

If an employee A has three rows in the View,i want my output in this format.
EmployeeNumber,ClientID,BillingCode1,AmountBilled1
,InvoiceNumber1,BillingCode2,AmountBilled2,Invoice
Number2,BillingCode3,AmountBilled3,InvoiceNumber3
etc..

I do not know in advance the maximum number of Invoices a employee may
have.There is no fixed limit on this.however,i have noticed in my database
that we do not have more than 5 till date.

How can I write a query to accomplish this?Please help.

Reply to this Message

KARL DEWEY - 28 Aug 2006 19:39 GMT

Try this using two queries, the first named [Jennyrd-1]--
[Jennyrd-1] ---
SELECT Jennyrd.EmployeeNumber, Jennyrd.InvoiceNumber, [ClientID] & " " &
[BillingCode] & " " & [AmountBilled] & " " & [InvoiceNumber] AS Expr1
FROM Jennyrd;

TRANSFORM First([Jennyrd-1].Expr1) AS FirstOfExpr1
SELECT [Jennyrd-1].EmployeeNumber
FROM [Jennyrd-1]
GROUP BY [Jennyrd-1].EmployeeNumber
PIVOT "Invoice Number: " & [InvoiceNumber];

> I have a view which selects these columns: employeenumber,ClientID,Billing
> code,amountBilled,InvoiceNumber from a number of other tables.
[quoted text clipped - 22 lines]
>
> How can I write a query to accomplish this?Please help.

Reply to this Message