One batch zhang SAN 1
A batch of li si 2
A group of fifty 3
A number of times the song six 3
Need to get the results
Batchname participants to report person approver
A group of fifty times "Tom, dick, and harry, song six
CodePudding user response:
Urgent urgent, bosses, please!CodePudding user response:
The CREATE TABLE # T
(BATCHNAME NVARCHAR (50),
The USERNAME NVARCHAR (20),
USERTYPE INT)
INSERT INTO # T
SELECT 'a number of times',' zhang SAN, 1 UNION ALL
SELECT 'a number of times',' li si, 2 UNION ALL
SELECT 'a number of times',' Cathy ', three UNION ALL
SELECT 'a number of times',' song 6 '3
WITH CTE
AS
(SELECT BATCHNAME USERTYPE,
STUFF ((SELECT the ', '+ USERNAME FROM # T WHERE a. ATCHNAME=a. ATCHNAME AND A.U SERTYPE=USERTYPE FOR XML PATH ('')), 1, 1, ' ') AS USER_GROUP
The FROM # T AS A
GROUP BY BATCHNAME, USERTYPE)
SELECT BATCHNAME, [1] AS report person, [2] AS the approver, [3] AS participants FROM CTE
The PIVOT
(MAX (USER_GROUP) FOR USERTYPE (IN [1], [2], [3])) AS B
CodePudding user response:
My novice writing
If the exists (select * from sysobjects where name='Table3')
Drop table Table3
The create table Table3
(
One nvarchar (20),
Two nvarchar (20),
Three nvarchar (20)
)
Insert into Table3
Values (' a number of times', 'zhang', '1')
, 'a number of times',' bill ', '2')
, (' a number of times', 'Cathy', '3')
, 'a number of times',' song six ', '3')
Select * from Table3
Select one, (select two from Table3 t2 where t2. One=t1. One and three='1') as report person, (select two from Table3 t2 where t2. One=t1. One and three='2') as the approver, STUFF ((select ', '+ two from Table3 t2 where t2. One=t1. One and three=' 3 'FOR XML path ('')), 1, 1, ' ') as participants from Table3 as t1 group by one