[SQL]將多筆資料合併為一筆顯示(FOR XML PATH)

用北風資料庫舉例

step1:先讓我們看看orderID = ‘10248’的產品有哪些

SELECT * FROM [Order Details] 
where OrderID = '10248'

有這些

step2:然後我們利用for xml path把他合併顯示

SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details] 
where OrderID = '10248'
FOR XML PATH('')

輸出是這樣，可以發現果然productID都合併了

step3:然後改成顯示所有的order的productID

SELECT OrderID,(SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details] 
where OrderID = ord.OrderID
FOR XML PATH('')) as productIDs
from orders ord
GROUP BY orderid

輸出像是這樣，所有的order的productID，但是productID還多一個逗號

step4:最後動些手腳，輸出就完美了，成功的將多筆資料合併為一筆

SELECT m.OrderID ,left(m.productIDs,len(m.productIDs)-1) as productIDsFinal from 
(SELECT OrderID,(SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details] 
where OrderID = ord.OrderID
FOR XML PATH('')) as productIDs
from orders ord
GROUP BY orderid) M --這個M一定要加，不知道為啥
ORDER by M.OrderID 

最後輸出: