[MSSQL] 使用for xml 將一對多欄位合併為一個字串

寫SQL時，常會需要將多筆相同欄位的資料合併成一個字串，這邊來記錄一下之前的解法。

Table

以兩張簡單的資料表(dbo.UserMain & dbo.UserRole)為範例

• dbo.UserMain

  UserID	UserName
  1	Davolio
  2	Fuller
  3	Leverling
  4	Peacock

• dbo.UserRole

  UserID	RoleName
  1	Boss
  2	PM
  2	SA
  3	PG
  4	SA
  4	SD
  4	PG

使用 FOR XML 語法合併欄位

SELECT
    U.UserID,
    U.UserName,
    ISNULL(STUFF(pRoleList.RoleValue, 1, 1, ''), '') AS Roles
FROM dbo.UserMain U
OUTER APPLY (
    SELECT
        ',' + UR.RoleName
    FROM dbo.UserRole UR
    WHERE U.UserID = UR.UserID
    FOR XML PATH ('')
) pRoleList (RoleValue)

• 使用OUTER APPLY將UserMain與UserRole關聯建立起來
• 使用FOR XML PATH¹將多筆結果以,合併為一筆顯示
• 使用 STUFF² 去除掉第一個多餘的逗號

  STUFF-可於字串指定位置插入指定字串
  STUFF(原字串, 起始位置, 移除長度, 插入字串)

查詢結果

UserID	UserName	Roles
1	Davolio	Boss
2	Fuller	PM,SA
3	Leverling	PG
4	Peacock	SA,SD,PG

參考資料

---

1. FOR XML (SQL Server) ↩︎

2. STUFF (Transact-SQL) ↩︎