SQL 行转列，列转行

SQL 行转列，列转行

行列转换在做报表分析是还是经常会遇到的
行列转换就是如下图所示两种展示形式互相转换

创建表

只是做测试，建表比较随意

create table student(
   name varchar(20),
	 subject int(10),
	 score int(10)
)

insert into student(name,subject,score) values ("小华",1,12);
insert into student(name,subject,score) values ("小华",2,13);
insert into student(name,subject,score) values ("小华",3,14);
insert into student(name,subject,score) values ("大华",1,35);
insert into student(name,subject,score) values ("大华",2,11);
insert into student(name,subject,score) values ("大华",3,20);

行转列

select name,
   max(
	   case 
		 when subject = 1 
		 then score 
		 else 0
	   end 
	 ) as 语文,
	 max(
	   case
		 when subject = 2
		 then score 
		 else 0
		 end
	 ) as 数学,
   max(
	   case
		 when subject = 3
		 then score 
		 else 0
		 end
	 ) as 英语
from student group by name;

列转行

select 
    name,
    '语文' as subject,
		max('语文') as score
from student group by name
UNION
select 
     name,
     '数学' as subject,
		 max('数学') as score
from student GROUP BY name
UNION
select 
     name,
     '英语' as subject,
		 max('英语') as score
from student GROUP BY name;