一、原始数据

二、行转列的多种实现方式

1.CASE WHEN

2.DECODE

3.PIVOT(Oracle独有)

4.使用LEAD开窗函数

三、列转行的多种实现方式

1.UNPIVOT(Oracle独有)

2.UNION ALL合并结果集

四、行转列练习：CASE WHEN/DECODE/PIVOT/lag/LEAD

1.CASE WHEN

2.DECODE

3.PIVOT

4.LEAD

一、原始数据

CREATE TABLE T_SCORE (SNO NUMBER,CLA VARCHAR2(20),SCORE NUMBER);INSERT INTO T_SCORE VALUES (101,'语文',88);
INSERT INTO T_SCORE VALUES (101,'数学',89);
INSERT INTO T_SCORE VALUES (101,'英语',90);
INSERT INTO T_SCORE VALUES (102,'语文',91);
INSERT INTO T_SCORE VALUES (102,'数学',77);
INSERT INTO T_SCORE VALUES (102,'英语',56);
INSERT INTO T_SCORE VALUES (103,'语文',77);
INSERT INTO T_SCORE VALUES (103,'数学',88);
INSERT INTO T_SCORE VALUES (103,'英语',99);
INSERT INTO T_SCORE VALUES (104,'语文',77);
INSERT INTO T_SCORE VALUES (104,'数学',66);
INSERT INTO T_SCORE VALUES (104,'英语',55);
INSERT INTO T_SCORE VALUES (105,'语文',44);
INSERT INTO T_SCORE VALUES (105,'数学',67);
INSERT INTO T_SCORE VALUES (105,'英语',78);
INSERT INTO T_SCORE VALUES (106,'语文',89);
INSERT INTO T_SCORE VALUES (106,'数学',98);
INSERT INTO T_SCORE VALUES (106,'英语',78);
INSERT INTO T_SCORE VALUES (107,'语文',67);
INSERT INTO T_SCORE VALUES (107,'数学',56);
INSERT INTO T_SCORE VALUES (107,'英语',54);
INSERT INTO T_SCORE VALUES (108,'语文',76);
INSERT INTO T_SCORE VALUES (108,'数学',78);
INSERT INTO T_SCORE VALUES (108,'英语',12);
COMMIT;SELECT * FROM T_SCORE;

二、行转列的多种实现方式

原格式：

行转列后的格式：

1.CASE WHEN

select SNO, CASE WHEN CLA = '语文' THEN SCORE end as 语文, CASE WHEN CLA = '数学' THEN SCORE end as 数学, CASE WHEN CLA = '英语' THEN SCORE end as 英语
from T_SCORE;

下面两种聚合函数都可以：

select SNO, max(CASE WHEN CLA = '语文' THEN SCORE end) as 语文, max(CASE WHEN CLA = '数学' THEN SCORE end) as 数学, max(CASE WHEN CLA = '英语' THEN SCORE end) as 英语
from T_SCORE
group by SNO
order by SNO;select SNO, sum(CASE WHEN CLA = '语文' THEN SCORE end) as 语文, sum(CASE WHEN CLA = '数学' THEN SCORE end) as 数学, sum(CASE WHEN CLA = '英语' THEN SCORE end) as 英语
from T_SCORE
group by SNO
order by SNO;

二者区别：

聚合函数	逻辑	处理重复记录	适用场景
`MAX`	返回分组内的最大值。若每组只有一个值，则直接返回该值。	保留最大值（如补考成绩）。	行转列（提取唯一值）。
`SUM`	返回分组内所有值的总和。若每组只有一个值，则返回该值本身。	累加所有值（可能导致成绩异常）。	统计总分或合计。

2.DECODE

select SNO, decode(CLA, '语文', SCORE) as 语文, decode(CLA, '数学', SCORE) as 数学, decode(CLA, '英语', SCORE) as 英语
from T_SCORE;

select SNO, max(decode(CLA, '语文', SCORE)) as 语文, max(decode(CLA, '数学', SCORE)) as 数学, max(decode(CLA, '英语', SCORE)) as 英语
from T_SCORE
group by SNO
order by SNO;select SNO, sum(decode(CLA, '语文', SCORE)) as 语文, sum(decode(CLA, '数学', SCORE)) as 数学, sum(decode(CLA, '英语', SCORE)) as 英语
from T_SCORE
group by SNO
order by SNO;

3.PIVOT(Oracle独有)

语法：

PIVOT (SUM(聚合值) FOR 待转换的列名 IN (待转换的列名里面的值 转换后列的别名))

select *
from T_SCOREPIVOT (sum(SCORE) for CLA in ( '语文' Chinese,'数学' Math,'英语' English))
order by SNO;

4.使用LEAD开窗函数

因为要对中文进行排序，所以先使用ASCII码

SELECT CLA, ASCII(CLA)
FROM T_SCORE
GROUP BY CLA
ORDER BY CLA;

SELECT *
FROM (SELECT sno, LEAD(score, 0) OVER (PARTITION BY sno ORDER BY CLA ) 数学, LEAD(score, 1) OVER (PARTITION BY sno ORDER BY CLA ) 英语, LEAD(score, 2) OVER (PARTITION BY sno ORDER BY CLA ) 语文FROM T_SCORE)
WHERE 语文 IS NOT NULL;

三、列转行的多种实现方式

DROP TABLE b_score;
CREATE TABLE b_score AS
SELECT *
FROM (SELECT *FROM t_scorePIVOT (SUM(score) -- 聚合函数（使用 SUM 或 MAX 均可）FOR cla IN ('语文' AS 语文, -- 指定课程名称及对应的列别名'数学' AS 数学,'英语' AS 英语)))
ORDER BY sno; -- 按学生编号排序
COMMIT;
SELECT *
FROM b_score;

原格式：

行转列后的格式：

1.UNPIVOT(Oracle独有)

语法：

UNPIVOT (存储指标值的列名 FOR 合并后的列名 IN (待合并的列名))

SELECT *
FROM b_scoreUNPIVOT (score FOR cla IN (语文,数学,英语));

2.UNION ALL合并结果集

SELECT sno,'语文' cla,语文 score FROM b_score
UNION ALL
SELECT sno,'数学' cla,数学 score FROM b_score
UNION ALL
SELECT sno,'英语' cla,英语 score FROM b_score
order by SNO;

四、行转列练习：CASE WHEN/DECODE/PIVOT/lag/LEAD

输出每个部门的总人数，展示样式：

D10 D20 D30

3 5 6

1.CASE WHEN

select COUNT(case when DEPTNO = 10 then 1 end) as D10, COUNT(case when DEPTNO = 20 then 1 end) as D20, COUNT(case when DEPTNO = 30 then 1 end) as D30
from EMP;

2.DECODE

select COUNT(DECODE(DEPTNO, 10, 1)) as D10, COUNT(DECODE(DEPTNO, 20, 1)) as D20, COUNT(DECODE(DEPTNO, 30, 1)) as D30
from EMP;

3.PIVOT

SELECT DEPTNO, COUNT(EMPNO) CM
FROM EMP
GROUP BY DEPTNO;

select *
from (SELECT DEPTNO, COUNT(EMPNO) CMFROM EMPGROUP BY DEPTNO)pivot (sum(cm) for DEPTNO in (10 D10,20 D20,30 D30));

4.LEAD

select DEPTNO, count(empno) ct
from EMP
group by DEPTNO;

SELECT *
FROM (select lead(ct, 0) over ( order by DEPTNO) as D10, lead(ct, 1) over ( order by DEPTNO) as D20, lead(ct, 2) over ( order by DEPTNO) as D30from (select DEPTNO, count(empno) ctfrom EMPgroup by DEPTNO)) a
WHERE D30 IS NOT NULL;