if object_id('tempdb..#list') is not null drop table #listselect [name]='张三',kecheng='a',fenshu=80,[year]=2014 into #list union all select [name]='张三',kecheng='b',fenshu=80,[year]=2016 union allselect [name]='李四',kecheng='c',fenshu=80,[year]=2015 union all select [name]='李四',kecheng='d',fenshu=80,[year]=2018 declare @col nvarchar(max)='',@sql nvarchar(max); with list as( select distinct [year] from #list ) select @col=@col+',['+cast([year] as nvarchar(50))+']' from list order by [year]print @col set @sql=' select * from (select [name],[year],fenshu=avg(fenshu) from #list group by [name],[year] ) a pivot(max(fenshu) for [year] in('+RIGHT(@col,len(@col)-1)+')) pt ' print @sql exec(@sql)/*name 2014 2015 2016 2018 ---- ----------- ----------- ----------- ----------- 李四 NULL 80 NULL 80 张三 80 NULL 80 NULL(2 行受影响) */
--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([name] nvarchar(22),[kecheng] nvarchar(22),[fenshu] int,[year] NVARCHAR(20)) Insert #T select N'张三',N'语文',82,'2014' union all select N'张三',N'数学',88,'2014' union all select N'张三',N'语文',73,'2015' union all select N'张三',N'数学',73,'2015' union all select N'李四',N'语文',80,'2014' union all select N'李四',N'数学',60,'2014' union all select N'李四',N'语文',78,'2015' union all select N'李四',N'数学',82,'2015' union all select N'王五',N'英语',90,'2016' Go --测试数据结束 DECLARE @sql VARCHAR(MAX) SET @sql = 'select Name' SELECT @sql = @sql + ',sum(case [year] when ''' + [year] + ''' then fenshu else 0 end)/(case when sum(case [year] when ''' + [year] + ''' then 1 else 0 end)=0 then 1 else sum(case [year] when ''' + [year] + ''' then 1 else 0 end) end)[' + [year] + '年]' FROM ( SELECT DISTINCT [year] FROM #T ) a SET @sql = @sql + ' from #T group by Name' EXEC(@sql)
if object_id('tempdb..#list') is not null drop table #listselect [name]='张三',kecheng='a',fenshu=80,[year]=2014 into #list union all
select [name]='张三',kecheng='b',fenshu=80,[year]=2016 union allselect [name]='李四',kecheng='c',fenshu=80,[year]=2015 union all
select [name]='李四',kecheng='d',fenshu=80,[year]=2018
declare @col nvarchar(max)='',@sql nvarchar(max);
with list as(
select distinct [year]
from #list
)
select @col=@col+',['+cast([year] as nvarchar(50))+']'
from list
order by [year]print @col
set @sql='
select *
from (select [name],[year],fenshu=avg(fenshu)
from #list
group by [name],[year]
) a pivot(max(fenshu) for [year] in('+RIGHT(@col,len(@col)-1)+')) pt
'
print @sql
exec(@sql)/*name 2014 2015 2016 2018
---- ----------- ----------- ----------- -----------
李四 NULL 80 NULL 80
张三 80 NULL 80 NULL(2 行受影响)
*/
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([name] nvarchar(22),[kecheng] nvarchar(22),[fenshu] int,[year] NVARCHAR(20))
Insert #T
select N'张三',N'语文',82,'2014' union all
select N'张三',N'数学',88,'2014' union all
select N'张三',N'语文',73,'2015' union all
select N'张三',N'数学',73,'2015' union all
select N'李四',N'语文',80,'2014' union all
select N'李四',N'数学',60,'2014' union all
select N'李四',N'语文',78,'2015' union all
select N'李四',N'数学',82,'2015' union all
select N'王五',N'英语',90,'2016'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select Name'
SELECT @sql = @sql + ',sum(case [year] when ''' + [year]
+ ''' then fenshu else 0 end)/(case when sum(case [year] when ''' + [year]
+ ''' then 1 else 0 end)=0 then 1 else sum(case [year] when ''' + [year]
+ ''' then 1 else 0 end) end)[' + [year] + '年]'
FROM ( SELECT DISTINCT
[year]
FROM #T
) a
SET @sql = @sql
+ ' from #T group by Name'
EXEC(@sql)