>Hello all,
>
>I have a table which lists work order reference numbers for a shipment reference number (both are int) as follows:
>
>
>ShpmID WOID
>21508 14839
>21508 15095
>21508 15254
>
>
>I would like to create a query that would return one row for the shipment id with a concatenated string list of work order ids as follows:
>
>
>ShpmID WORefNbrs
>21508 14839, 15095, 15254
>
>
>How can I create a query to do this?
>
>As always, TIA
>
>Bob
Try
SELECT S.ShpmID, stuff((select ', ' + convert(varchar(20),WOID)
FROM Shipments S1 where S1.ShmID = S.ShpmID order by WOID
FOR XML PATH('')),1,2,'') as [WORefNbrs]
FROM Shipments S
GROUP BY S.ShpmID
See these blogs for explanations:
MSDN thread about concatenating rowsMaking a list and checking it twiceConcatenating Rows - Part 1Concatenating Rows - Part 2
If it's not broken, fix it until it is.
My Blog