我想问下这道题怎么做解决思路

[qq_32132265]

跪求 主要不确定年份人员课程

[Dear SQL(燊)]

sql: 先算出每人每年的平将成绩，再用动态SQL将年份行转列（函数：pivot）

[Dear SQL(燊)]

if object_id('tempdb..#list') is not null drop table #list

select [name]='张三',kecheng='a',fenshu=80,[year]=2014 into #list union all
select [name]='张三',kecheng='b',fenshu=80,[year]=2016 union all

select [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)