Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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

Fiddle

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

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

Your Answer

 
discard

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.