By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,417 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,417 IT Pros & Developers. It's quick & easy.

Consolidate information from multiple rows into one row

P: 1
Hi,
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.
Jul 5 '06 #1
Share this Question
Share on Google+
1 Reply


P: 2
I'm also interested in a solution to this type of problem.

I have a project with multiple committees (title, date) where there can be a maximum of 6 committees for a given project.

I tried to use rownum to do this, but ran into a problem where I pull only the first 6 committee titles/dates into my extract.

Bumping this one to hope that it can get a response.

Thanks,

GT
Nov 22 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.