|
以sample資料庫Northwind為例，一個OrderID可對應多筆訂單。
如何能將訂單結果以一對多的方式輸出？
這個需求可透過XML format輸出來達成。
將Select出來的結果加上逗號分隔，
以預設的FOR XML PATH進行格式化輸出，不帶任何參數輸出結果如下
SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details]
where OrderID = '10248'
FOR XML PATH
參數改以空字串取代XML elements後，結果如下
SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details]
where OrderID = '10248'
FOR XML PATH('')
把這樣的FOR XML PATH('')輸出樣式搭配訂單主表後，結果如下
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
再透過小技巧left(欄位,len(欄位)-1)修掉最後的逗點，結果就達陣囉～
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
ORDER By M.OrderID
PS.
FOR XML參數在SQL 2005後才可使用。
♥順子老婆的網拍，請多關照～
If you don't like something, change it.
If you can't change it, change your attitude.
Don't complain!
|
2013-04-23, 15:50
《分享》透過XML format輸出一對多結果
FOR XML不同參數的輸出結果
