[SQL] 利用FOR XML子句合併多筆相同欄位資料

2014-12-17

3516
0
T-SQL
2016-11-09

利用FOR XML子句合併多筆相同欄位資料

問題簡述

以下是常見的帳號角色關聯資料表，其中帳號及角色分別紀錄於Users與Roles資料表中，在透過UsersRole資料表建立起帳號與角色之關聯。接著需求是這樣，我們需要將每個帳號的角色列出，並且用分隔符號隔出顯示於畫面上；此時腦中立即浮現了許多複雜的方式來處理，就在搜尋前人智慧後發現，SQL2005後可利用FOX XML子句特性來合併多筆欄位資料，以下將簡單介紹及實作FOX XML來達成需求。

解決方法

其實FOR XML子句用途就是在將資料轉換為XML格式字串，透過簡單的範例就可知道其特性了；在此先撈出所有角色的資料，並在最後加上For Xml Path('MyRole')，執行後即可將各筆資料轉換成XML格式。

執行後資料為XML格式字串

整理一下此字串，其實Xml就是就是依照各欄位名稱及自訂的資料行名稱來建立Tag

<MyRole>
  <RoleId>1</RoleId>
  <Name>PM</Name>
  <IsEnable>1</IsEnable>
</MyRole>
<MyRole>
  <RoleId>2</RoleId>
  <Name>Sales</Name>
  <IsEnable>1</IsEnable>
</MyRole>

比較一下我們熟悉的資料呈現方式，是不是很有感覺呢?

接著，我們可以透過此特性把各資料行中的資料進行合併。首先，移除不需要的欄位，只保留我們所需要的角色名稱欄位；再來移除掉資料行名稱Tag，直接設定For Xml Path('')，由於我們只希望用逗號間隔開個角色名稱，所以可以透過字串相加產生無欄位名稱的特性，間接移除掉欄位名稱Tag，執行結果如下。

最後，我們已大致了解Xml For作用了，所以就直接寫出符合需求得查詢邏輯即可

SELECT UserId,
STUFF((
	-- 使用FOR XML PATH 組合資料
	SELECT ',' + r.Name FROM dbo.Users u
	INNER JOIN dbo.UsersRoles ur ON ur.UserId = u.UserId
	INNER JOIN dbo.Roles r ON r.RoleId = ur.RoleId
	where u.UserId = Users.UserId
	for xml path('')
),1,1,'') AS Roles
FROM dbo.Users

執行結果如預期地條列出各帳號的角色資訊