MSSQL 动态列转行并统计

来源：互联网发布：java大数据方向学什么编辑：程序博客网时间：2024/05/17 22:44

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 ) tbPIVOT (       avg(result)      FOR [subject] IN    (' + @sqlc1 + ')  ) AS T1 ) as t1left join (select [name], '+ @sqlc1 +' from (select [name], [RefResult], [subject] from tb ) tbPIVOT (       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 ) tbPIVOT (       avg(result)      FOR [subject] IN    (' + @sqlc1 + ')  ) AS T1 ) as t1left join (select [name], '+ @sqlc1 +' from (select [name], [RefResult], [subject] from tb ) tbPIVOT (       avg(RefResult)      FOR [subject] IN    ( '+ @sqlc1 + ')  ) AS T2 ) as T2 on T1.name = T2.name'set @sql = @sqls1 + ' union all ' + @sqls2exec(@sql)

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

0 0