sql 行列转置(将行转换为列)

闲着没事写起耍

目的：将科目列转换为行

1、创建表

create  table [testTable]( 
 id int identity(1,1) primary key,
 name varchar(20),         
 [subject]  varchar(20),
 score dec (18,2)
)

insert into [testTable] values ('张三','语文',41)
insert into [testTable] values ('张三','英语',94)
insert into [testTable] values ('张三','数学',89)
insert into [testTable] values ('张三','物理',96)
insert into [testTable] values ('张三','生物',90)

insert into [testTable] values ('李四','语文',63)
insert into [testTable] values ('李四','英语',81)
insert into [testTable] values ('李四','数学',82)
insert into [testTable] values ('李四','物理',52)
insert into [testTable] values ('李四','生物',82)

insert into [testTable] values ('王五','语文',98)
insert into [testTable] values ('王五','英语',99)
insert into [testTable] values ('王五','数学',97)
insert into [testTable] values ('王五','物理',97)
insert into [testTable] values ('王五','生物',91)

insert into [testTable] values ('赵四','语文',98)
insert into [testTable] values ('赵四','英语',98)
insert into [testTable] values ('赵四','数学',97)
insert into [testTable] values ('赵四','物理',96)
insert into [testTable] values ('赵四','生物',95)

insert into [testTable] values ('李芳','语文',80)
insert into [testTable] values ('李芳','英语',65)
insert into [testTable] values ('李芳','数学',85)
insert into [testTable] values ('李芳','物理',56)
insert into [testTable] values ('李芳','生物',88)

insert into [testTable] values ('周杰','语文',50)
insert into [testTable] values ('周杰','英语',95)
insert into [testTable] values ('周杰','数学',85)
insert into [testTable] values ('周杰','物理',89)
insert into [testTable] values ('周杰','生物',80)

--drop table [testTable]

普通查询结果：

select * from [testTable]

2、进行行列转换(将科目转换为列)

DECLARE @s varchar(8000)
set @s='select a.name'
select @s=@s+','+REPLACE([subject],' ','')+'=sum((case a.subject when '''+[subject]+''' then a.score end))' from [testTable] group by subject
set @s=@S+' from testTable a group by a.name'
exec (@s)
print @s

结果如下：