一.建表语句
create table kecheng ( id NUMBER, name VARCHAR2(20), course VARCHAR2(20), score NUMBER ); insert into kecheng (id, name, course, score) values (1, '张三', '语文', 67); insert into kecheng (id, name, course, score) values (1, '张三', '数学', 76); insert into kecheng (id, name, course, score) values (1, '张三', '英语', 43); insert into kecheng (id, name, course, score) values (1, '张三', '历史', 56); insert into kecheng (id, name, course, score) values (1, '张三', '化学', 11); insert into kecheng (id, name, course, score) values (2, '李四', '语文', 54); insert into kecheng (id, name, course, score) values (2, '李四', '数学', 81); insert into kecheng (id, name, course, score) values (2, '李四', '英语', 64); insert into kecheng (id, name, course, score) values (2, '李四', '历史', 93); insert into kecheng (id, name, course, score) values (2, '李四', '化学', 27); insert into kecheng (id, name, course, score) values (3, '王五', '语文', 24); insert into kecheng (id, name, course, score) values (3, '王五', '数学', 25); insert into kecheng (id, name, course, score) values (3, '王五', '英语', 8); insert into kecheng (id, name, course, score) values (3, '王五', '历史', 45); insert into kecheng (id, name, course, score) values (3, '王五', '化学', 1); commit;
二、固定行转成列
2.1 decode方法
SELECT id, NAME, SUM(decode(course, '语文', score, 0)) 语文,--这里使用max,min都可以 SUM(decode(course, '数学', score, 0)) 数学, SUM(decode(course, '英语', score, 0)) 英语, SUM(decode(course, '历史', score, 0)) 历史, SUM(decode(course, '化学', score, 0)) 化学 FROM kecheng GROUP BY id, NAME;
2.2 case when
SELECT ID,NAME,MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语,MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史,MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学FROM kechengGROUP BY ID ,NAME
三、动态转换行列
DECLARE --存放最终的SQL LV_SQL VARCHAR2(3000); --存放连接的SQL SQL_COMMOND VARCHAR2(3000); --定义游标 CURSOR CUR IS SELECT COURSE FROM KECHENG GROUP BY COURSE;BEGIN --定义查询开头 SQL_COMMOND := 'SELECT NAME '; FOR I IN CUR LOOP --将结果相连接 SQL_COMMOND := SQL_COMMOND || ' ,SUM(DECODE(course,''' || I.COURSE || ''',score,0)) ' || I.COURSE; DBMS_OUTPUT.PUT_LINE(SQL_COMMOND); END LOOP; SQL_COMMOND := SQL_COMMOND || ' from KECHENG group by name'; LV_SQL := 'INSERT INTO temp_ss ' || SQL_COMMOND; DBMS_OUTPUT.PUT_LINE(LV_SQL); EXECUTE IMMEDIATE LV_SQL;END;
