Post reply

merge multiple rows into one

  • November 26, 2014 at 9:51 am

    #313406

    i have no idea how to do this, basically i have data like this

    order_key comment

    1 A

    1 B

    1 C

    2 B

    2 D

    the data intends to be like this

    order_key comment

    1 A,B,C

    2 B,D

    anyone can you help?

  • November 26, 2014 at 10:09 am

    #1761139

    Here is one way:

    declare @t table (i int, c char(1))

    insert into @t (i,c) values (1,'a'),(1,'b'),(1,'c'),(2,'a'),(2,'b')

    select * from @t;

    With MyCTE as (

    select distinct i, (select t2.c + ',' from @t as t2 where t1.i = t2.i for xml path('')) as Letters

    from @t t1)

    select i, left(Letters, len(Letters) - 1)

    from MyCTE

    go

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 26, 2014 at 4:03 pm

    #1761180

    Another twist (although does basically the same thing)

    declare @t table (order_key int, comment char(1))

    insert into @t (order_key, comment)

    values (1,'a'),(1,'b'),(1,'c'),(2,'a'),(2,'b')

    select

    order_key,

    stuff((select ',' + comment

    from @t i

    where i.order_key = o.order_key

    for xml path ('')), 1,1,'')

    from @t o

    group by order_key

    The query plans are slightly different but on such a small sample set, performance is identical. if one suits your fancy more than the other, use that one.

    Executive Junior Cowboy Developer, Esq.[/url]

  • November 27, 2014 at 6:09 am

    #1761298

    thanks for the reply, it really helps.

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

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