Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6
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/