练习列转行

最新推荐文章于 2025-05-25 22:31:47 发布

原创最新推荐文章于 2025-05-25 22:31:47 发布 · 469 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

4 篇文章

订阅专栏

if not object_id('Class') is null  
    drop table Class  
Go  
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)  
Insert Class  
select N'张三',N'语文',78 union all  
select N'张三',N'数学',87 union all  
select N'张三',N'英语',82 union all  
select N'张三',N'物理',90 union all  
select N'李四',N'语文',65 union all  
select N'李四',N'数学',77 union all  
select N'李四',N'英语',65 union all  
select N'李四',N'物理',85   
Go  

select * from class

---静态
select 
     student,
[语文]=max(case when course ='语文' then [score] else 0 end),
[数学]=max(case when course ='数学' then [score] else 0 end),
[英语]=max(case when course ='英语' then [score] else 0 end),
[物理]=max(case when course ='物理' then [score] else 0 end),
[总成绩]=sum([Score])
  from class 
group by student

---动态
declare @s nvarchar(4000)  
set @s=''  
Select  @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'  
from Class group by[Course]  
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')

---静态
select  course ,  
       [张三]=max( case when student='张三' then [score] else 0 end),
       [李四]=max( case when student='李四' then [score] else 0 end),
       [科目平均分]=avg([score])
from class 
group by course


---动态
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename([student])+'=max(case when [student]='+quotename([student],'''')+' then [score] else 0 end)'
from class group by [student]
exec ('select course'+@s+',[科目平均分]=avg(score) from class group by [course]'  )