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


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.

1 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   
             , 1, 1, '' )
from t
GROUP BY SalesOrderNumber


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.