SqlServer技巧:多行合并一行
-- 数据准备
CREATE TABLE work(
id int,
name nvarchar(100),
work nvarchar(100)
)
insert into work
values('1','张','经理')
insert into work
values('2','张','开发人员')
insert into work
values('3','李','职员')
insert into work
values('4','李','HR')
SELECT * FROM work
数据准备
--多行合并一行
select a.name,STUFF((SELECT ','+t1.work FROM work t1 WHERE a.name=t1.name FOR XML PATH('')), 1, 1, '')
from work a GROUP BY a.name
结果:
-- 含义拆解:
-- for XML PATH 可实现多行变一行; ','+ 将'<>'变为','
select ','+work from work for XML PATH('')
-- STUFF ( character_expression , start , length , replaceWith_expression )
-- 将字符串中第一个','去掉,也可以通过RIGHT()或SUBSTRING()实现--但是比较麻烦
SELECT STUFF(',经理,开发人员', 1, 1, '')