T-SQL筆記12_如何透過for xml path 搭配STUFF將多ROW資料合併同一ROW

- 11月 11, 2020

透過for xml path 搭配STUFF將多ROW資料合併同一ROW

首先產生測試用的資料

SQL:

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (1, N'Lisa') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (2, N'Fanny') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (2, N'Doris') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (3, N'Kiki') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (3, N'John') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (1, N'Lily') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (1, N'Matt') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (3, N'Tina') 

INSERT [TEAMLIST] ([team_id], [emp_name])  
VALUES (1, N'Andy')

以下是我們的測試資料

團隊編號對應員工名字

SQL:

select emp_name from TEAMLIST where team_id=1 
for xml path('')
--<emp_name>Lisa</emp_name><emp_name>Lily</emp_name><emp_name>Matt</emp_name><emp_name>Andy</emp_name>
select ','+emp_name from TEAMLIST where team_id=1 
for xml path('')
--,Lisa,Lily,Matt,Andy
select emp_name+',' from TEAMLIST where team_id=1 
for xml path('')
--Lisa,Lily,Matt,Andy,

select ','+emp_name from TEAMLIST for xml path('')
--,Lisa,Fanny,Doris,Kiki,John,Lily,Matt,Tina,Andy

預設的for xml path 會直接將對應該column下所有的row值變成一串By該欄位名稱的XML字串

在欄位串接特定字元 (這裡用逗號)

就會替換成用該字元join起來

這裡改一下SQL來達成功能需求要的

顯示團隊ID跟相應的員工名稱列表(同團隊ID的要將員工名稱合併為同一列)

SQL:

select distinct team_id , (
	select ',' + emp_name from TEAMLIST as inner_list
	where inner_list.team_id = outer_list.team_id 
	for xml path('')
) as team_members
from TEAMLIST as outer_list

看起來仍有稍稍美中不足前面多出逗號

此時用

STUFF(原字串, 起始位置, 移除長度, 替換字串)

來修飾

SQL:

select distinct team_id ,STUFF((
	select ',' + emp_name from TEAMLIST as inner_list
	where inner_list.team_id = outer_list.team_id 
	for xml path('')
),1,1,'') as team_members
from TEAMLIST as outer_list