Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!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.

share|improve this question
up vote 1 down vote accepted

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

share|improve this answer
brilliant, thank you so much – Peter PitLock Jul 4 '14 at 12:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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