create table test1(
	id int identity(1,1) not null,
	name varchar(255) null,
	course varchar(255) null,
	score int null,
)

insert into test1(name, course, score) values ('張三','語文', 80)
insert into test1(name, course, score) values ('張三','數(shù)學', 52)
insert into test1(name, course, score) values ('張三','英語', 150)
insert into test1(name, course, score) values ('李四','語文', 44)
insert into test1(name, course, score) values ('李四','數(shù)學', 111)
insert into test1(name, course, score) values ('李四','英語', 110)
insert into test1(name, course, score) values ('王五','語文', 140)
insert into test1(name, course, score) values ('王五','數(shù)學', 80)
insert into test1(name, course, score) values ('王五','英語', 92)
insert into test1(name, course, score) values ('王五','物理', 77)
insert into test1(name, course, score) values ('王五','化學', 65)

-- 使用case when then else ，這里也可以使用sum函數(shù)
select name,
max(case course when '語文' then score else 0 end) as chinese,
max(case course when '數(shù)學' then score else 0 end) as math,
max(case course when '英語' then score else 0 end) as english,
max(case course when '物理' then score else 0 end) as wuli,
max(case course when '化學' then score else 0 end) as huaxue
from test1 group by name

-- 使用pivot函數(shù)行轉列
select name,max(t.語文)as chinese,max(t.數(shù)學)as math,max(t.英語)as english,max(t.物理)as wuli,max(t.化學)as huaxue
from test1  pivot(max(score) for course in(語文,數(shù)學,英語,物理,化學))t 
group by name

-- 第一種寫法，動態(tài)sql拼接，有多少行可以進行動態(tài)拼接sql，在列不確定的情況下可以使用
declare @sql_str varchar(8000); -- 要執(zhí)行的sql
declare @sql_col varchar(8000);
select @sql_col = isnull(@sql_col + ',','') + quotename(course) 
from test1 group by course;
print(@sql_col); -- 打印數(shù)值列,不必需
set @sql_str = 'select * from (select name,course,score from test1)p pivot(sum(score) for course IN ( '+ @sql_col +'))as pvt order by pvt.name'
print (@sql_str);--打印執(zhí)行的sql
exec (@sql_str);-- 執(zhí)行查詢

--第二種寫法
declare @name varchar(100);
declare @max varchar(1000);
declare @sql nvarchar(4000);
select @name= stuff(
(select ','+course+'' from test1  group by course  for xml path('')),1,1,'');
select @max= stuff(
(select ',max('+course+') as '+course+'' from test1 group by course for xml path('')),1,1,'');
set @sql='select name,'+@max+' from test1 pivot (max(score) for course in('+@name+')) css   group by name';
exec(@sql);

-- 使用distinct
select distinct  a.name,  
(select score from test1 b where a.name=b.name and b.course='語文' ) as 'chinese',  
(select score from test1 b where a.name=b.name and b.course='數(shù)學' ) as 'math',
(select score from test1 b where a.name=b.name and b.course='英語' ) as 'english',  
(select score from test1 b where a.name=b.name and b.course='物理' ) as 'wuli',  
(select score from test1 b where a.name=b.name and b.course='化學' ) as 'huaxue'  
from test1 a 

create table test2(
	id int identity(1,1) not null,
	name varchar(255) null,
	chinese int null,
	math int null,
	english int null,
	wuli int null,
	huaxue int null
) 

insert into test2(name,chinese,math,english,wuli,huaxue) values ('張三',110,120,85,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values ('李四',130,88,89,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values ('王五',93,124,87,98,67);

--第一種寫法:	
select row_number() over(order by id desc) as id,name,t.course,t.score from(
	select id,name,course='語文',score=chinese from test2 union all
	select id,name,course='數(shù)學',score=math from test2 union all
	select id,name,course='英語',score=english from test2 union all
	select id,name,course='物理',score=wuli from test2 union all
	select id,name,course='化學',score=huaxue from test2
) t where score is not null order by id asc
-- 下面可以不用執(zhí)行，執(zhí)行上面即可
,case t.course when '語文' then 1 when '數(shù)學' then 2 when '英語' then 3 when '物理' then 4 when '化學' then 5 end

-- 第二種寫法：
select row_number() over(order by id desc) as id,name,t.course,t.score from(
	select id,name,'語文' as course, chinese as 'score' from test2 union 
	select id,name,'數(shù)學' as course, math as 'score' from test2 union 
	select id,name,'英語' as course, english as 'score' from test2 union 
	select id,name,'物理' as course, wuli as 'score' from test2 union 
	select id,name,'化學' as course, huaxue as 'score' from test2
) t where score is not null order by id asc
-- 下面可以不用執(zhí)行，執(zhí)行上面即可
,case t.course when '語文' then 1 when '數(shù)學' then 2 when '英語' then 3 when '物理' then 4 when '化學' then 5 end

--使用unpivot進行列轉行
select row_number() over(order by id desc) as id,name,score,course from test2 unpivot( score for course  in(chinese,math,english,wuli,huaxue))a