Q:

SQL same unit between two tables needs order numbers in 1 cell

I have 2 tables:
SELECT UnitId FROM dbo.tblUnits

SELECT UnitId, WorkOrderNumber FROM dbo.tblWorkOrders

I need to display all UnitId's from dbo.tblUnits then in 1 column diplay all the WorkOrders seperated by a comma.
So here is some sample data:
dbo.tblUnits:
UnitId
123
156
178

dbo.tblWorkOrders
UnitId WorkOrderNumber
123        1
123        2
156        4
178        5
178        9
178        10

I have to use the tblUnits table because I am pulling more data from it but the final result I want to show this:
UnitId   WorkOrderNumber
123         1,2
156         4 
178         5,9,10

Any Ideas?
Thanks

A:

select 
    UnitId, 
    stuff((select ', ' + convert(varchar, WorkOrderNumber) 
           from tblWorkOrders t2 where t1.UnitId = t2.UnitId 
           for xml path('')),
          1,2,'') WorkOrderNumbers
from tblWorkOrders t1
group by UnitId

