数据分析面试题三：行列转换

一、行转列(图中左变右)
1，题目要求(图中左变右)：

2，创建表

create table course (
id varchar(20),
teacher_id varchar(20),
week_day varchar(20),
has_course varchar(20)
);

3，插入数据：

insert into course value
(1,1,2,"Yes"),
(2,1,3,"Yes"),
(3,2,1,"Yes"),
(4,3,2,"Yes"),
(5,1,2,"Yes")
;

查询结果：

 select * from course;

4，行转列SQL实现：
– 行转列

select id,teacher_id,
(case when week_day = 1 then "Yes" else " " end) "mon",
(case when week_day = 2 then "Yes" else " " end) "tue",
(case when week_day = 3 then "Yes" else " " end) "thi",
(case when week_day = 4 then "Yes" else " " end) "thu",
(case when week_day = 5 then "Yes" else " " end) "fri"
 from course;

查询结果：

方法二：

select id,teacher_id,
(case week_day when 1 then "Yes" else " " end) "mon",
(case week_day when 2 then "Yes" else " " end) "tue",
(case week_day when 3 then "Yes" else " " end) "thi",
(case week_day when 4 then "Yes" else " " end) "thu",
(case week_day when 5 then "Yes" else " " end) "fri"
 from course;

查询结果：

二、列转行(图中左变右)
1，题目要求(图中左变右)：
通过SQL语句，将表A格式转换为表B格式

2，创建表并插如数据

create table a1 (
name varchar(20),
english int,
maths int,
music int);
insert into a1 values
("Jim",90,88,99);

3，SQL实现

SELECT name, "english" AS subject, english AS score
FROM a1
UNION
SELECT name, "maths" AS subject, maths AS score
FROM a1
UNION
SELECT name, "music" AS subject, music AS score
FROM a1
ORDER BY name;