用一张学生成绩来举例：从老师的角度，在录入成绩时，每科老师都会单独录入每个学生的本科成绩。而从学生的角度，学生只关心自己各科的成绩分别是多少。所以如果把老师录入数据作为原始表，那么学生查看自己的成绩时就要用到行转列，如果让学生上报自己各科的成绩，然后老师去查对应学科的学生考试成绩时，那就是列转行了。

1、行转列示例

1）创建实验表（行存表）

--创建实验表（行存表） CREATE TABLE grade( name VARCHAR(10) ,course VARCHAR(10) ,score INT); --初始化测试数据 INSERT INTO grade VALUES ('张三','数学',80); INSERT INTO grade VALUES ('张三','英语',88); INSERT INTO grade VALUES ('张三','语文',95); INSERT INTO grade VALUES ('李四','数学',88); INSERT INTO grade VALUES ('李四','英语',70); INSERT INTO grade VALUES ('李四','语文',93); --查看结果 SELECT * FROM grade ORDER BY course;

2）静态行转列

使用sum、case when的方式：

--静态行转列 SELECT name ,sum(case when course = '数学' then score else 0 end) AS "数学" ,sum(case when course = '英语' then score else 0 end) AS 英语 ,sum(case when course = '语文' then score else 0 end) AS 语文 FROM grade GROUP BY name;

3）行转列（结果值：拼接式）

使用listagg within group：

--行转列（结果值：拼接式） SELECT name, LISTAGG(score,',') WITHIN GROUP (ORDER BY course) FROM grade GROUP BY name;

4）动态行转列（拼接SQL式）

通过“listagg + 创建FUNCTION + VIEW”的方式实现

--动态行转列（SQL拼接式） SELECT listagg(concat('SUM(CASE WHEN course = ''', course, ''' THEN score ELSE 0 END) AS "', course,'"'),',') WITHIN GROUP(ORDER BY 1) AS concat_text FROM (SELECT DISTINCT course FROM grade); --concat_text的结果： SUM(CASE WHEN course = '数学' THEN score ELSE 0 END) AS "数学",SUM(CASE WHEN course = '英语' THEN score ELSE 0 END) AS "英语",SUM(CASE WHEN course = '语文' THEN score ELSE 0 END) AS "语文" --创建一个函数。 CREATE OR REPLACE FUNCTION fun_test() RETURNS VOID LANGUAGE SQL AS $$ DECLARE s_sql text; rec record; BEGIN s_sql := 'SELECT listagg(CONCAT(''SUM(CASE WHEN course = '''''', course, '''''' THEN score ELSE 0 END) AS "'', course, ''"'' ),'','' ) WITHIN GROUP(ORDER BY 1) AS concat_text FROM (SELECT DISTINCT course FROM grade);'; EXECUTE s_sql INTO rec; s_sql := 'DROP VIEW IF EXISTS v_score; CREATE VIEW v_score AS SELECT name, ' || rec.concat_text || ' FROM grade GROUP BY name;'; EXECUTE s_sql; END $$; --调用 CALL fun_test(); --查看执行结果 select * from v_score;

Tip：请注意SQL拼写时的英文单引号、双引号。

2、列转行示例

1）创建实验表（复用前面的测试数据）

--创建实验表（复用前面的测试数据） CREATE TABLE grade1 AS SELECT name ,sum(case when course = '数学' then score else 0 end) AS "数学" ,sum(case when course = '英语' then score else 0 end) AS 英语 ,sum(case when course = '语文' then score else 0 end) AS 语文 FROM grade GROUP BY name; --查看结果 SELECT * FROM grade1;

2）使用union all，将各科目（数学、英语、语文）整合为一列

--使用union all，将各科目（数学、英语、语文）整合为一列 SELECT * FROM ( SELECT name, '数学' AS course, 数学 AS score FROM grade1 union all SELECT name, '英语' AS course, 英语 AS score FROM grade1 union all SELECT name, '语文' AS course, 语文 AS score FROM grade1 ) order by name;