摘要:多筆資料串成一個欄位
SQL 2005的T-SQL語法把XML給加進來,
可以直接以SQL語法將多筆資料串再一起
例如:
| ColA | ColB |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
變成
| 1 | 1,2,3 |
例如:
Use Northwind
Select OrderId,
(
Select p.ProductName + ',' From [Order Details] od
Inner Join Products p on od.ProductID = p.ProductID
Where o.OrderID = od.OrderID
For XML PATH('')
)
from Orders o
Where OrderDate Between '1997-1-1' And '1997-1-5'
1997-1-1到1997-1-5間的訂單資料
| OrderId | Products |
| 10400 | Thuringer Rostbratwurst,Steeleye Stout,Maxilaku, |
| 10401 | Nord-Ost Matjeshering,Gnocchi di nonna Alice,Louisiana Fiery Hot Pepper |
| 10402 | Sauce,Flotemysost, |
| 10403 | Pavlova,Chocolade, |
| 10404 | Gumbar Gummibarchen,Singaporean Hokkien Fried Mee,Maxilaku, |