Employee name completion project completed category value
Zhang SAN project A 50 61%
Zhang SAN project B 60 70%
Zhang SAN 80% C 70
Li si A 55 67%
Li si project 45 78% B
Li si 80% C 76


To the following:
Name the project A complete project B values completion rate values completion project C complete completion
Zhang SAN 50 60 61% 70%, 70 80%
Li si 55 67% 45 78%, 76 80%

CodePudding user response:

suggest building Lord raise junction post rate
The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T ([employee name] nvarchar (22), [items] nvarchar (23), the complete value [] nvarchar (23), [completion] nvarchar (23)) 
Insert # T 
Select N 'Joe', N 'project A, 50, N' 61% 'union all 
Select N 'Joe', N 'project B, 60, N' 70% 'union all 
Select N 'Joe', N 'project C, 70, N' 80% 'union all 
Select N 'bill', N 'A' project, 55, N '67%' union all 
Select N 'bill', N 'project B, 45, N' 78% 'union all 
Select N 'bill', N 'project C, 76, N' 80% '
Go 
- the end of the test data 
DECLARE @ SQL VARCHAR (MAX) 
The SET @ SQL='the select [employee name]' 
SELECT SQL + SQL=@ @ ', Max (case/project categories when "' + [items] 
+ "' then complete value [] else null end) [' + [items] + 'complete value]' + ', Max (case/project categories when" '+ [items] 
+ "' then [completion] else null end) [' + [items] + 'completion]' 
The FROM (SELECT DISTINCT 
[the project category] 
The FROM # T 
) a 
SQL=SET @ @ SQL 
+ 'from # T group by [employee name]' 
The EXEC (@ SQL)