batchname username usertype
一批次 張三 1
一批次 李四 2
一批次 王五 3
一批次 宋六 3
需得到結果
batchname 匯報人 審批人 參與人
一批次 張三 李四 王五、宋六
uj5u.com熱心網友回復：
急急急，請大佬！uj5u.com熱心網友回復：
CREATE TABLE #T
(BATCHNAME NVARCHAR(50),
USERNAME NVARCHAR(20),
USERTYPE INT)
INSERT INTO #T
SELECT '一批次','張三',1 UNION ALL
SELECT '一批次','李四',2 UNION ALL
SELECT '一批次','王五',3 UNION ALL
SELECT '一批次','宋六',3
WITH CTE
AS
(SELECT BATCHNAME,USERTYPE,
STUFF((SELECT '、'+USERNAME FROM #T WHERE A.BATCHNAME=A.BATCHNAME AND A.USERTYPE=USERTYPE FOR XML PATH('')),1,1,'') AS USER_GROUP
FROM #T AS A
GROUP BY BATCHNAME,USERTYPE)
SELECT BATCHNAME,[1] AS 匯報人,[2] AS 審批人,[3] AS 參與人 FROM CTE
PIVOT
(MAX(USER_GROUP) FOR USERTYPE IN ([1],[2],[3])) AS B
uj5u.com熱心網友回復：
我的菜鳥寫法
if exists(select * from sysobjects where name='Table3')
Drop table Table3
create table Table3
(
one nvarchar(20),
two nvarchar(20),
three nvarchar(20)
)
insert into Table3
values('一批次','張三','1')
,('一批次','李四','2')
,('一批次','王五','3')
,('一批次','宋六','3')
select * from Table3
select one ,(select two from Table3 t2 where t2.one=t1.one and three='1' ) as 匯報人 ,(select two from Table3 t2 where t2.one=t1.one and three='2' ) as 審批人,STUFF((select ','+ two from Table3 t2 where t2.one=t1.one and three='3' FOR XML path('')),1,1,'' ) as 參與人 from Table3 as t1 group by one
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/284716.html
標籤：基礎類