数据表行转列

View Code

 1 create table AllScore(
 2 
 3 id int identity(1,1) primary key,--主键
 4 stu_name varchar(20),--学生名
 5 category varchar(20),--学科
 6 record float,--成绩
 7 )
 8 /*插入数据*/
 9 insert into AllScore(stu_name,category,record)values('刘德华','Chinese',100)
10 insert into AllScore(stu_name,category,record)values('刘德华','English',95)
11 insert into AllScore(stu_name,category,record)values('刘德华','Mathmatics',98)
12 
13 insert into AllScore(stu_name,category,record)values('施瓦辛格','Chinese',90)
14 insert into AllScore(stu_name,category,record)values('施瓦辛格','English',95)
15 insert into AllScore(stu_name,category,record)values('施瓦辛格','Mathmatics',14)
16 
17 insert into AllScore(stu_name,category,record)values('太上老君','Chinese',70)
18 insert into AllScore(stu_name,category,record)values('太上老君','English',95)
19 insert into AllScore(stu_name,category,record)values('太上老君','Mathmatics',57)
20 
21 insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Chinese',60)
22 insert into AllScore(stu_name,category,record)values('毕达哥拉斯','English',95)
23 insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Mathmatics',68)
24 
25 insert into AllScore(stu_name,category,record)values('柏拉图','Chinese',60)
26 insert into AllScore(stu_name,category,record)values('柏拉图','English',95)
27 insert into AllScore(stu_name,category,record)values('柏拉图','Mathmatics',78)
28 
29 insert into AllScore(stu_name,category,record)values('亚里士多德','Chinese',40)
30 insert into AllScore(stu_name,category,record)values('亚里士多德','English',22)
31 insert into AllScore(stu_name,category,record)values('亚里士多德','Mathmatics',25)
32 
33 insert into AllScore(stu_name,category,record)values('卢梭','Chinese',40)
34 insert into AllScore(stu_name,category,record)values('卢梭','English',50)
35 insert into AllScore(stu_name,category,record)values('卢梭','Mathmatics',78)
36 
37 insert into AllScore(stu_name,category,record)values('老庄','Chinese',100)
38 insert into AllScore(stu_name,category,record)values('老庄','English',20)
39 insert into AllScore(stu_name,category,record)values('老庄','Mathmatics',98)
40 
41 -----开始行转列-------
42 declare @sql nvarchar(4000)
43 set @sql='Select stu_name '
44 Select @sql=@sql+',sum(case when category='''+category+ ''' then Record else 0 end) As '''+category+''''
45 From AllScore Group By category
46 set @sql=@sql+' From AllScore Group By stu_name'
47 Print @sql
48 execute sp_executesql @sql

View Code

 1 select *from test
 2 insert into test(score,name,subject) values(80,'李四','语文');
 3 insert into test(score,name,subject) values(60,'李四','数学');
 4 insert into test(score,name,subject) values(90,'李四','英语');
 5 insert into test(score,name,subject) values(80,'张三','语文');
 6 insert into test(score,name,subject) values(60,'张三','数学');
 7 insert into test(score,name,subject) values(90,'张三','英语');
 8 
 9 -----------------------------------------------
10 
11 select max(name) as 姓名, 
12     max(case
13            when subject ='语文' then score
14         end )as 语文 ,
15     max(case
16            when subject ='数学' then score
17         end )as 数学 ,
18     max(case
19            when subject ='英语' then score
20         end )as 英语 
21 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