数据表行转列

create table AllScore(

id int identity(1,1) primary key,--主键
stu_name varchar(20),--学生名
category varchar(20),--学科
record float,--成绩
)
/*插入数据*/
insert into AllScore(stu_name,category,record)values('刘德华','Chinese',100)
insert into AllScore(stu_name,category,record)values('刘德华','English',95)
insert into AllScore(stu_name,category,record)values('刘德华','Mathmatics',98)

insert into AllScore(stu_name,category,record)values('施瓦辛格','Chinese',90)
insert into AllScore(stu_name,category,record)values('施瓦辛格','English',95)
insert into AllScore(stu_name,category,record)values('施瓦辛格','Mathmatics',14)

insert into AllScore(stu_name,category,record)values('太上老君','Chinese',70)
insert into AllScore(stu_name,category,record)values('太上老君','English',95)
insert into AllScore(stu_name,category,record)values('太上老君','Mathmatics',57)

insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Chinese',60)
insert into AllScore(stu_name,category,record)values('毕达哥拉斯','English',95)
insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Mathmatics',68)

insert into AllScore(stu_name,category,record)values('柏拉图','Chinese',60)
insert into AllScore(stu_name,category,record)values('柏拉图','English',95)
insert into AllScore(stu_name,category,record)values('柏拉图','Mathmatics',78)

insert into AllScore(stu_name,category,record)values('亚里士多德','Chinese',40)
insert into AllScore(stu_name,category,record)values('亚里士多德','English',22)
insert into AllScore(stu_name,category,record)values('亚里士多德','Mathmatics',25)

insert into AllScore(stu_name,category,record)values('卢梭','Chinese',40)
insert into AllScore(stu_name,category,record)values('卢梭','English',50)
insert into AllScore(stu_name,category,record)values('卢梭','Mathmatics',78)

insert into AllScore(stu_name,category,record)values('老庄','Chinese',100)
insert into AllScore(stu_name,category,record)values('老庄','English',20)
insert into AllScore(stu_name,category,record)values('老庄','Mathmatics',98)

-----开始行转列-------
declare @sql nvarchar(4000)
set @sql='Select stu_name '
Select @sql=@sql+',sum(case when category='''+category+ ''' then Record else 0 end) As '''+category+''''
From AllScore Group By category
set @sql=@sql+' From AllScore Group By stu_name'
Print @sql
execute sp_executesql @sql

select *from test
insert into test(score,name,subject) values(80,'李四','语文');
insert into test(score,name,subject) values(60,'李四','数学');
insert into test(score,name,subject) values(90,'李四','英语');
insert into test(score,name,subject) values(80,'张三','语文');
insert into test(score,name,subject) values(60,'张三','数学');
insert into test(score,name,subject) values(90,'张三','英语');

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

select max(name) as 姓名, 
    max(case
           when subject ='语文' then score
        end )as 语文 ,
    max(case
           when subject ='数学' then score
        end )as 数学 ,
    max(case
           when subject ='英语' then score
        end )as 英语 
from test group by name

 

--方法一：
select distinct name as '姓名' ,(select score from test where name=t.name and subject='语文') as '语文' ,
(select score from test where name=t.name and subject='数学') as '数学',
(select score from test where name=t.name and subject='英语') as '英语'
from test as t

--方法二：
select max(name) as 姓名, 
    max(case
           when subject ='语文' then score
        end )as 语文 ,
    max(case
           when subject ='数学' then score
        end )as 数学 ,
    max(case
           when subject ='英语' then score
        end )as 英语 
from test group by name

----方法三：
declare @sql nvarchar(max);
set @sql='select name as 姓名'
select @sql=@sql+',max(case when subject= '''+subject+''' then score end) as '+subject from test group by subject
select @sql=@sql+' from test group by name'
print @sql
exec sp_executesql @sql
select *from test