0

I have a table that looks something like the folowing

ID     LINE_NUMBER  CODE          DATA
39311   1           AXFP-20090    A
39311   2           AXFP-20227    B
39311   3           AXFP-20067    B
39311   4           AXFP-20337    B
39311   5           AXFP-20081    B
39311   6           AXFP-20048    B
39311   7           AXFP-20025    B
39311   8           AXFP-22371    B
39311   9           AXFP-20176    B
39311   10          AXFP-20278    B
39311   11          AXFP-21838    B
39311   14          AXFP-92035    C
41681   1           AXFP-99999    A
41681   2           AXFP-58229    A
41681   3           AXFP-51166    A
41681   5           AXFP-21848    C
41681   6           AXFP-58191    C
41681   7           AXFP-61481    C
41681   8           AXFP-21757    A
41681   9           AXFP-32704    C
41681   10          AXFP-60037    B
41681   11          AXFP-21849    B
41681   12          AXFP-34021    C
41681   13          AXFP-34072    C

I need to combine the data for each id into one row. To hold the line number, code and Data in the following format

enter image description here

Anyone have any ideas how I could do this?

psycho
  • 1,539
  • 4
  • 20
  • 36
  • You need a group concat: http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server – Jimmy T. Oct 23 '15 at 13:06

1 Answers1

0
select  ID     ,
Data=Stuff((SELECT ' ' + CAST(LINE_NUMBER  as VARCHAR(255)) + ')' + CODE  +  ' ' + DATA  FROM t t1 WHERE t1.ID =t.ID        
 FOR XML PATH (''))
             , 1, 1, '' )
from t
GROUP BY ID

FIDDLE

Mihai
  • 26,325
  • 7
  • 66
  • 81