SQL Server 行列转换

在SQL SERVER中，有时需要合多列值到行的需求，常见的方法有：1.创建自定义函数，2.使用游标法进行字符串合并。3.使用临时表实现字符串合并。 数据： DEPTNO EMPS
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD
期望结果：
DEPTNO EMPS
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
1.xmloutput---------------------------------------
SELECT *
from(
SELECT DISTINCT
deptno
from emp
)A
OUTER APPLY(
SELECT
[values]= STUFF(REPLACE(REPLACE(
(
SELECT ENAME from emp N
WHERE deptno = A.deptno
FOR XML AUTO
), '', ''), 1, 1, '')
)N
 
2.---using CTE----------------------------------------
--Creating a Delimited List from Table Rows
-------------------------------------------------------
with x (deptno, cnt, list, empno, len)
as (
select deptno, count(*) over (partition by deptno),
cast(ename as varchar(100)),
empno,
1
from emp
union all
select x.deptno, x.cnt,
cast(x.list + ',' + e.ename as varchar(100)),
e.empno, x.len+1
from emp e, x
where e.deptno = x.deptno
and e.empno > x. empno
)
select deptno,list
from x
where len = cnt
order by 1
3.....