列转行--行转列

问题：使用case when实现行转列

解决：

1、测试准备：

CREATE TABLE StudentScores(

UserName NVARCHAR2(20), --学生姓名

Subject NVARCHAR2(30), --科目

Score FLOAT --成绩

);

2、准备数据：

INSERT INTO StudentScores values ('Nick', '语文', 80);

INSERT INTO StudentScores values ('Nick', '数学', 90);

INSERT INTO StudentScores values ('Nick', '英语', 70);

INSERT INTO StudentScores values ('Nick', '生物', 85);

INSERT INTO StudentScores values ('Kent', '语文', 80);

INSERT INTO StudentScores values ('Kent', '数学', 90);

INSERT INTO StudentScores values ('Kent', '英语', 70);

INSERT INTO StudentScores values ('Kent', '生物', 85);

commit;

目前的数据形式为：

目标的数据形式：

3、实现方式：

SELECT

UserName,

MAX(CASE WHEN Subject='语文' THEN Score ELSE 0 END) AS "语文",

MAX(CASE WHEN Subject='数学' THEN Score ELSE 0 END) AS "数学",

MAX(CASE WHEN Subject='英语' THEN Score ELSE 0 END) AS "英语",

MAX(CASE WHEN Subject='生物' THEN Score ELSE 0 END) AS "生物"

FROM StudentScores

GROUP BY UserName;

讨论：

实现上述需求的关键在于，多个case when与group by的配合使用；下面我们来

分析下sql的执行过程：

我们手下看下select子句，可以确定要查询的结果集中有5列，username、语文、数学、英

语、生物；拿出表中第一条记录多数据处理得出的结果集：

Username 语文 数学 英语 生物

Nick 80 0 0 0

80是由CASE WHEN Subject='语文' THEN Score ELSE 0 END得出，其他的0分别是由CASE WHEN

Subject='数学' THEN Score ELSE 0 END、CASE WHEN Subject='英语' THEN Score ELSE 0 END、

CASE WHEN Subject='生物' THEN Score ELSE 0 END得出，一次类推，得到的结果集为

Username 语文 数学 英语 生物

Nick 80 0 0 0

Nike 0 90 0 0

Nike 0 0 70 0

Nike 0 0 0 85

Kent 80 0 0 0

Kent 0 90 0 0

Kent 0 0 70 0

Kent 0 0 0 85

下一步，聚合分组，最终完成任务。

令一种实现方式：

SELECT

UserName,

sum(decode(subject,'语文',score,0)) AS "语文",

sum(decode(subject,'数学',score,0)) AS "数学",

sum(decode(subject,'英语',score,0)) AS "英语",

sum(decode(subject,'生物',score,0)) AS "生物"

FROM StudentScores

GROUP BY UserName;

问题：实现列转行

解决：

1、准备数据：

create table ScoresStudent as SELECT

UserName,

sum(decode(subject,'语文',score,0)) AS "语文",

sum(decode(subject,'数学',score,0)) AS "数学",

sum(decode(subject,'英语',score,0)) AS "英语",

sum(decode(subject,'生物',score,0)) AS "生物"

FROM StudentScores

GROUP BY UserName;

目前的数据形式：

目标数据形式：

2、实现方式：

select username,'语文' as subject,"语文" as score from ScoresStudent

union

select username,'数学' as subject,"数学" as score from ScoresStudent

union

select username,'英语' as subject,"英语" as score from ScoresStudent

union

select username,'生物' as subject,"生物" as score from ScoresStudent;

4楼dorria昨天 22:41

很好 顶下

3楼xiao007ming昨天 12:35

gooooooooooood

2楼fantingftt昨天 11:33

不错，顶一下！

1楼lidaasky昨天 11:25

学习了