Post reply

Consolidating certain columns of Multiple rows into one row

  • July 5, 2006 at 3:07 pm

    #173301

    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.

  • July 6, 2006 at 3:30 pm

    #647850

    If SQL 2005, I think you could create a custom aggregator in the CLR which concatenates strings together, then use a GROUP BY with your new aggregator to do the concatenation. But what if you want to control the order of the concatenation? Perhaps if your custom aggregator had a parameter to control the order of concatenation, and then that parameter would become the index key of an ordered collection (eg. a .net OrderedDictionary), from which you would extract the elements when the final value of the concatenation aggregator is needed by the Terminate method.

    http://www.devsource.com/article2/0,1895,1821863,00.asp?rsDis=SQL_Server_2005_CLR_Integration_Part_2:_Working_with_Data-Page002-153058

    In SQL 2000, I think you'd have to use a cursor, or a set of case statements with subqueries (limited to a finite number of columns).

    But as Joe says, this is normally done on the client.

     

  • July 17, 2006 at 8:02 am

    #649559

    What you want is basically a pivot table or cross table.

    In SS2005 you have it automatically, otherwise have a look on this forum a search for those keywords like pivot, cross table, turning rows into columns etc



    Bye
    Gabor

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply