建表语句
CREATE TABLE
TBL_A
(
NAME VARCHAR2(50),
SUBJECT VARCHAR2(50),
GRADE NUMBER(10,2)
)
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES ( ‘张三’, ‘语文’, 90);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES ( ‘张三’, ‘数学’, 68);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘张三’, ‘英语’, 65);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES ( ‘张三’, ‘历史’, 67);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘张三’, ‘化学’, 56);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES ( ‘王五’, ‘语文’, 91);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES ( ‘王五’, ‘数学’, 88);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘王五’, ‘英语’, 88);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘王五’, ‘历史’, 78);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘王五’, ‘化学’, 90);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES ( ‘赵六’, ‘语文’, 56);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES ( ‘赵六’, ‘数学’, 78);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘赵六’, ‘英语’, 76);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘赵六’, ‘历史’, 56);
INSERT INTO TBL_A ( NAME, SUBJECT, GRADE)VALUES (‘赵六’, ‘化学’, 78);
数据查询如图：
行转列第一种方式 SUM+DECODE函数：
SELECT NAME AS 姓名,
SUM(DECODE(SUBJECT,‘语文’,GRADE,0)) as 语文,
SUM(DECODE(SUBJECT,‘数学’,GRADE,0)) as 数学,
SUM(DECODE(SUBJECT,‘英语’,GRADE,0)) as 英语,
SUM(DECODE(SUBJECT,‘历史’,GRADE,0)) as 历史,
SUM(DECODE(SUBJECT,‘化学’,GRADE,0)) as 化学
FROM TBL_A
GROUP BY NAME
行转列第二种方式 MAX+DECODE函数：
SELECT NAME AS 姓名,
MAX(DECODE(SUBJECT,‘语文’,GRADE,0)) AS 语文,
MAX(DECODE(SUBJECT,‘数学’,GRADE,0)) AS 数学,
MAX(DECODE(SUBJECT,‘英语’,GRADE,0)) AS 英语,
MAX(DECODE(SUBJECT,‘历史’,GRADE,0)) AS 历史,
MAX(DECODE(SUBJECT,‘化学’,GRADE,0)) AS 化学
FROM TBL_A
GROUP BY NAME
行转列第三种方式 MAX+CASE WHEN函数：
SELECT NAME AS 姓名,
MAX(CASE WHEN SUBJECT=‘语文’ THEN GRADE ELSE 0 END)AS 语文,
MAX(CASE WHEN SUBJECT=‘数学’ THEN GRADE ELSE 0 END)AS 数学,
MAX(CASE WHEN SUBJECT=‘英语’ THEN GRADE ELSE 0 END) AS 英语,
MAX(CASE WHEN SUBJECT=‘历史’ THEN GRADE ELSE 0 END)AS 历史,
MAX(CASE WHEN SUBJECT=‘化学’ THEN GRADE ELSE 0 END) AS 化学
FROM TBL_A
GROUP BY NAME
得到的结果如图：
多行转字符串第一种方方式 WMSYS.WM_CONCAT 函数：
SELECT NAME, WMSYS.WM_CONCAT (SUBJECT ||’:’||GRADE) AS SUBJECT
FROM TBL_A
GROUP BY NAME
多行转字符串第二种方方式 LISTAGG() WITHIN GROUP()函数：
SELECT NAME,LISTAGG(SUBJECT||’:’||GRADE,’,’ ) WITHIN GROUP(ORDER BY NAME) AS SUBJECT
FROM TBL_A GROUP BY NAME
得到的结果如图：
列转行
建表
CREATE TABLE TBL_B(
NAME VARCHAR2(50),
CHINESE NUMBER(12,2),
MATHEMATICS NUMBER(12,2),
ENGLISH NUMBER(12,2),
HISTORY NUMBER(12,2),
CHEMISTRY NUMBER(12,2)
)
数据查询如图：
语句：
SELECT NAME , ‘语文’ AS SUBJECT, CHINESE AS GRADE FROM TBL_B
UNION ALL
SELECT NAME , ‘数学’ AS SUBJECT, MATHEMATICS AS GRADE FROM TBL_B
UNION ALL
SELECT NAME , ‘英文’ AS SUBJECT, ENGLISH AS GRADE FROM TBL_B
UNION ALL
SELECT NAME , ‘历史’ AS SUBJECT, HISTORY AS GRADE FROM TBL_B
UNION ALL
SELECT NAME , ‘化学’ AS SUBJECT, CHEMISTRY AS GRADE FROM TBL_B
得到的结果如图：