I have this SQL statement:
x
SELECT
bs.ITEMNAME AS Item,
bom.ITEM AS PartNumber,
STUFF(SELECT ed.NOTE AS [text()]
WHERE bom.ITEM = ed.itemid
FOR XML PATH ('')) AS Description
FROM
vwAXBOM bom
LEFT JOIN
S2BOMSTR bs ON bom.POSITION = bs.ITEMNUM
LEFT JOIN
vwExtendedDescriptionMFG ed ON bom.ITEM = ed.itemid
WHERE
bom.BOMITEM = @partNum
AND bom.POSITION LIKE 'S%'
AND bom.POSITION != 'S76'
It returns a table like:
| Item | PartNumber | Description
+------+------------+--------------
| Film | 1234 | Some thing
| Film | 1234 | Other thing
| Flap | 5678 | Another thing
| Flap | 5678 | Final Thing
But I am looking to have something like this:
| Item | PartNumber | Description
+------+------------+---------------------------
| Film | 1234 | Some thing, Other thing
| Flap | 5678 | Another thing, Final thing
How do I change my SQL statement to accomplish this?
Advertisement
Answer
The solution to this problem ended up being to create a temp table for the main query. Then pulling the appropriate data out of the temp table utilizing the FOR XML PATH () function.
SELECT
bs.ITEMNAME AS Item
,bom.ITEM AS PartNumber
INTO #tempBomList
FROM vwAXBOM bom
LEFT JOIN S2BOMSTR bs
ON bom.POSITION = bs.ITEMNUM
LEFT JOIN vwExtendedDescriptionMFG ed
ON bom.ITEM = ed.itemid
WHERE bom.BOMITEM = @partNum AND bom.POSITION LIKE 'S%' AND bom.POSITION <> 'S76'
GROUP BY bs.ITEMNAME
,bom.ITEM
SELECT tb.Item
,tb.PartNumber
,(SELECT NOTE + ', '
FROM vwExtendedDescriptionMFG
WHERE ITEMID = tb.PartNumber
FOR XML PATH('')) AS Description
FROM #tempBomList tb