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

SELECT m.LOT ,left(m.TEMP_ARTICLE_NO,len(m.TEMP_ARTICLE_NO)-1) as ARTICLE_NO from (
SELECT LOT,(SELECT TEMP_ARTICLE_NO + ',' from 
(SELECT LOT,TEMP_ARTICLE_NO FROM YARN_IN_D WHERE YYYYMM='201608' AND STATUS<>'D' GROUP BY LOT,TEMP_ARTICLE_NO) a1 
where LOT = ord.LOT
FOR XML PATH('')) as TEMP_ARTICLE_NO
from (SELECT LOT FROM YARN_IN_D WHERE YYYYMM='201608' AND STATUS<>'D' GROUP BY LOT) ord
GROUP BY LOT
) M 
ORDER by M.lot

引用

SELECT cast(批號 AS NVARCHAR ) + ',' from [批號] 
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 

最後輸出: