none
Format query similar Pivot

    Question

  • Hi guys, one little question, how can I transform this table:

    1 x
    1 r
    1 t
    2 y
    2 x
    2 r
    2 t

    In this format:

    1 x,r,t

    2 y,x,r,t

    I already did it, but I don't remember in this moment...just an advice please.

    Thanks

    Saturday, August 03, 2013 11:05 AM

Answers

  • try this,

    declare @temp table(id int, [name] char(1))
    insert into @temp values (1,'x')
    insert into @temp values (1,'a')
    insert into @temp values (1,'c')
    insert into @temp values (3,'x')
    insert into @temp values (3,'u')
    SELECT [id], 
           Stuff((SELECT ',' + [name] 
                  FROM   @temp 
                  WHERE  [id] = a.[id] 
                  FOR xml path('')), 1, 1, '') [name]
    FROM   @TEMP a 
    GROUP  BY  [id]


    Regards, RSingh

    Saturday, August 03, 2013 11:22 AM