【行列互换】

行列互换问题

1.行转列

create table table2(year int,month int ,amount double) ;

insert into table2
values (1991, 1, 1.1), (1991, 2, 1.2), (1991, 3, 1.3), (1991, 4, 1.4), (1992, 1, 2.1), (1992, 2, 2.2), (1992, 3, 2.3), (1992, 4, 2.4);

将上图数据的展现形式转换成下图

思路:select框架的为下面这样，只需填充xx，可以用if或者case when end语句生成新列；（case when end为对列操作的函数，所以取值和赋值只要按行看过去）
并且需要对年份分类，意味着需要group by year.

select year,
xx as month1,
xx as month2,
xx as month3,
xx as month4

1.1两步法解决问题：

首先尝试搭建框架

select year,
case when month=1 then amount else 0 end as 'month1',
case when month=2 then amount else 0 end as 'month2',
case when month=3 then amount else 0 end as 'month3',
case when month=4 then amount else 0 end as 'month4'
from table2

结果如下

然后只需对上面的结果进行group by汇总求和。上图中的0也可以换成null。因为对单列求和自动忽略0。

select year,sum(month1),sum(month2),sum(month3),sum(month4)
 from(
select year,
case when month=1 then amount else 0 end as 'month1',
case when month=2 then amount else 0 end as 'month2',
case when month=3 then amount else 0 end as 'month3',
case when month=4 then amount else 0 end as 'month4'
from table2) as temp
GROUP BY year

结果如下：

1.2一步到位

select year,
sum(case when month=1 then amount else null end) as 'month1',
sum(case when month=2 then amount else null end) as 'month2', 
sum(case when month=3 then amount else null end) as 'month3', 
sum(case when month=4 then amount else null end) as 'month4'
from table2
group by year

总结：行转列问题，需要用到group by，聚合函数sum，count，case when end。

2.列转行问题

CREATE Table sql_3(
    name varchar(10),
    数学 FLOAT,
    语文 FLOAT,
    英语 FLOAT
);
INSERT INTO sql_3 (name,语文,数学,英语) values ('张三',78,88,98);
INSERT INTO sql_3 (name,语文,数学,英语) values ('李四',48,68,58);
INSERT INTO sql_3 (name,语文,数学,英语) values ('王五',100,99,97);
select * from sql_3;

将上图中的表转换成下图的格式

思路

将上表分别按取出名字，科目列，并生成字符串填充的新列科目。然后竖着拼在一起
意味着用到union函数

select name,'语文' as 科目,语文 as 成绩 from sql_3
UNION
select name,'数学' as 科目,数学 as 成绩 from sql_3
UNION
select name,'英语' as 科目,英语 as 成绩 from sql_3 ;