练习列转行

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

samue86

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

阅读量469

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/samue86/article/details/8633796

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]'  )