练习列转行

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

------------------------------------------------------------------------------------

上面涉及到quotename

  quotename函数有几种写法： 
  quotename('aa') 生成的有效的标识符为 [aa] 
  quotename('aa','') 生成的有效的标识符为 [aa] 
  quotename('aa','''') 生成的有效的标识符为 'aa'