【SQL】行转列

用户表：user

 

 

 

部门表：department

 

 

 

需求：

通过user表的部门ID，获得真正的部门名称且在一个列中显示，多个部门之间用，隔开

1、将行转列

SELECT b.dep_id
FROM (
    SELECT dep_id=CAST('<v>'+REPLACE(dep_id,',','</v><v>')+'</v>' AS xml) FROM user
    ) a
OUTER APPLY (SELECT dep_id= x.XmlCol.value('.','varchar(50)') FROM a.dep_id.nodes('/v') AS x(XmlCol)) b

查询结果如下图：   （实际可对user表做ID限制即可）

 

 

2、获取对应的部门

select d.dep_name from (
SELECT b.dep_id
FROM (
    SELECT dep_id=CAST('<v>'+REPLACE(dep_id,',','</v><v>')+'</v>' AS xml) FROM (select dep_id from user where user_id = 41) as z
    ) a
OUTER APPLY (SELECT dep_id= x.XmlCol.value('.','varchar(50)') FROM a.dep_id.nodes('/v') AS x(XmlCol)) b
)  c left join department as d on c.Dep_id = d.dep_id

 

 

3、最终结果（再把行转一列）

SELECT a.dep_name +',' FROM 
(
select d.dep_name from (
SELECT b.dep_id
FROM (
SELECT dep_id=CAST('<v>'+REPLACE(dep_id,',','</v><v>')+'</v>' AS xml) FROM (select dep_id from user where user_id = 41) as z
) a
OUTER APPLY (SELECT dep_id= x.XmlCol.value('.','varchar(50)') FROM a.dep_id.nodes('/v') AS x(XmlCol)) b
) c left join be_department as d on c.Dep_id = d.dep_id
) as a FOR XML PATH('')

查询结果如下：

 

 

 最后再用stuff函数对字符串做处理即可

 

参考：https://stackoverflow.com/questions/41778596/get-xml-node-from-sql-server-query