TSQL 將多筆相同鍵值的欄位合併

以下範例是Advanturework資料庫
有兩個資料表

主資料表:Purchasing.PurchaseOrderHeader


從資料表:Purchasing.PurchaseOrderDetail,FK:PurchaseOrderID

我們要把相同PurchaseOrderID的ProductID 串起來

要怎麼做呢?，SQL 2005 之後提供For XML PATH('') 指令可以將欄位串接
這裡我們可以先針對Purchasing.PurchaseOrderDetail的
PurchaseOrderID = 7
進行串接
select  ',' + cast(ProductID as varchar)
   from Purchasing.PurchaseOrderDetail a
   where PurchaseOrderID = 7
   for xml path('')
結果串起來了

接著把上面這段SQL當成Purchasing.PurchaseOrderHeader的子查詢就可以把
PurchaseOrderID的ProductID 串起來
select PurchaseOrderID,
  (select  ',' + cast(ProductID as varchar)
   from Purchasing.PurchaseOrderDetail a
     where a.PurchaseOrderID = b.PurchaseOrderID
     for xml path('')
    )
as PIDs
from Purchasing.PurchaseOrderHeader b
order by PurchaseOrderID

用Stuff指令把第一位','去除
select PurchaseOrderID,
stuff(
  (select  ',' + cast(ProductID as varchar)
   from Purchasing.PurchaseOrderDetail a
     where a.PurchaseOrderID = b.PurchaseOrderID
     for xml path('')
    )
   ,1,1,'') as PIDs
from Purchasing.PurchaseOrderHeader b
order by PurchaseOrderID
就大功告成了

後續
如果細讀MSDN的xml path的功用是把table資料轉成XML
<table>
<col1>11</col>
</table>
<table>
<col1>22</col>
</table>

但我們xml path後加上('')會把table tag省略:
<col1>11</col>
<col1>22</col>
又用了一招select ',' + cast(ProductID as varchar) 不取別名column 的tag也會被省略
就串起來了:
1122
另外，舊版Sql 2000的做法，只能用Union，請參考以下
Concatenate the values in a column in SQL Server 2000 and 2005