sql 行列转换

有如下表数据

id name course score
1 张三 语文 80
2 张三 数学 90
3 张三 英语 90
4 李四 语文 47
5 李四 数学 78
6 王五 数学 97

想要生成如下报表

name 数学 英语 语文
李四 78 NULL 47
王五 97 NULL NULL
张三 90 90 80

有如下方式

1. select S.name,
sum(case when course='语文' then score else null end) as 语文,
sum(case when course='数学' then score else null end) as 数学,
sum(case when course='英语' then score else null end) as 英语 from Score_Result S group by s.name

 

2. select S.name,
sum(case when course='语文' then score else null end) as 语文,
sum(case when course='数学' then score else null end) as 数学,
sum(case when course='英语' then score else null end) as 英语 from Score_Result S group by s.name

 

3.

USE [ScoreDB]
GO
/****** Object:  StoredProcedure [dbo].[convert_row_column]    Script Date: 06/10/2016 21:41:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[convert_row_column]
as
declare @course_count int
declare @sql varchar(1000)
declare @course_name varchar(20)
declare @index integer

set @course_count = (select COUNT(distinct course) from Score_Result)
set @index = 0
set @sql = 'select S.name '

while @index < @course_count
begin
 if @index=0
 begin
  set @course_name = (select distinct top 1 course from Score_Result)
 end
 else
 begin
  set @course_name = (select distinct top 1 course from Score_Result S where S.course not in( select distinct top (@index) course from Score_Result))
 end
 
 set @sql = @sql + ', sum(case when course=''' + @course_name + ''' then score else null end) as ' + @course_name
 
 set @index = @index + 1
end

set @sql = @sql + ' from Score_Result S group by s.name'

exec (@sql)

 

第三种的优势是即使增加了课程，也不必修改存储过程