MSSQL 动态列转行并统计

Create table tb([No] nvarchar(250),[Name] nvarchar(250),[RefResult] int,[Subject] nvarchar(250),[Result] int)
Insert tb
select N'01',N'张三',75,N'科目1',74 union all
select N'01',N'张三',85,N'科目2',90 union all
select N'01',N'张三',95,N'科目3',93 union all
select N'02',N'李四',75,N'科目1',78 union all
select N'02',N'李四',85,N'科目2',84 union all
select N'02',N'李四',98,N'科目3',100

----------------------------------------------------------------------------------------------------------------------------------------

declare @sqlc1 nvarchar(1000)
declare @sqlc2 nvarchar(1000)
declare @sqlc3 nvarchar(1000)
declare @sqls1 nvarchar(2000)
declare @sqls2 nvarchar(2000)
declare @sql nvarchar(4000)
SET @sqlc1=STUFF((SELECT N','+QUOTENAME(b.[Subject]) 
 FROM (select  distinct [Subject] from tb) as b
   FOR XML PATH('')),1,1,N'');
SET @sqlc2=STUFF((SELECT N', t1.' + QUOTENAME(b.[Subject]) 
    + ', t2.' + QUOTENAME(b.[Subject]) + ' as ' + QUOTENAME(b.[Subject] + '参考')
+ ', (t1.' + QUOTENAME(b.[Subject]) + ' - t2.'+QUOTENAME(b.[Subject]) 
+ ') as ' +  QUOTENAME(b.[Subject] + '差异')  
 FROM (select  distinct [Subject] from tb) as b
   FOR XML PATH('')),1,1,N'');
SET @sqlc3=STUFF((SELECT N', sum(t1.' + QUOTENAME(b.[Subject]) 
    + '), sum(t2.' + QUOTENAME(b.[Subject])
+ '), sum(t1.' + QUOTENAME(b.[Subject]) + ' - t2.'+QUOTENAME(b.[Subject]) + ')'  
 FROM (select  distinct [Subject] from tb) as b
   FOR XML PATH('')),1,1,N'');


SET @sqls1 = '
select 
  ROW_NUMBER() OVER(order by t1.[name]) as [NO], t1.[name], ' + @sqlc2 +
' from
(
select [name],' + @sqlc1 +
' from (select [name], [result], [subject] from tb ) tb
PIVOT 
(   
    avg(result)  
    FOR [subject] IN
    (' + @sqlc1 + ')  
) AS T1 
) as t1
left join 
(
select [name], '+ @sqlc1 +
' from (select [name], [RefResult], [subject] from tb ) tb
PIVOT 
(   
    avg(RefResult)  
    FOR [subject] IN
    ( '+ @sqlc1 + ')  
) AS T2 
) as T2 
on T1.name = T2.name'


SET @sqls2 = '
select 
  '''', ''合计'', ' + @sqlc3 + 
' from
(
select [name],' + @sqlc1 +
' from (select [name], [result], [subject] from tb ) tb
PIVOT 
(   
    avg(result)  
    FOR [subject] IN
    (' + @sqlc1 + ')  
) AS T1 
) as t1
left join 
(
select [name], '+ @sqlc1 +
' from (select [name], [RefResult], [subject] from tb ) tb
PIVOT 
(   
    avg(RefResult)  
    FOR [subject] IN
    ( '+ @sqlc1 + ')  
) AS T2 
) as T2 
on T1.name = T2.name'


set @sql = @sqls1 + ' union all ' + @sqls2
exec(@sql)

NO name 科目1 科目1参考 科目1差异 科目2 科目2参考 科目2差异 科目3 科目3参考 科目3差异
1 李四 7875 3 84 85 -1100 98 2
2 张三 7475 -1 90 85 593 95 -2
0 合计 152150 2 174 170 4 193 1930