Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6

Question

I have the following demo data (taken from Adventureworks)

SalesOrderNumber    SalesOrderDetailID
SO43659             12
SO43659             11
SO43659             8
SO43659             9
SO43660             14
SO43660             13
SO43661             27

Is it possible to Group per SalesOrderNumber, and show the Detail IDs per SalesOrderNumber?

SalesOrderNumber    SalesOrderDetailID
SO43659             12, 11, 8, 9
SO43660             14, 13
SO43661             27

http://www.sqlfiddle.com/#!3/7708b/1

What would be the best option for this please? Pivots? Cursors?

Cursor which calls stored proc for each row , e.g. here ? Or this option from 4guysfromrolla which uses user-defined function?

This is something that will be run once on only - at night - I dont need efficiency - just any help please.

Answer 1

This is standard sql server emulation of GROUP_CONCAT in mysql

select  SalesOrderNumber,Ids=Stuff((SELECT ',' + CAST(SalesOrderDetailID as VARCHAR(255)) FROM t t1 WHERE t1.SalesOrderNumber=t.SalesOrderNumber   
 FOR XML PATH (''))
             , 1, 1, '' )
from t
GROUP BY SalesOrderNumber

Fiddle

Also,you might be intrested in http://groupconcat.codeplex.com/