【18】Oracle 行列转换

一、建表与插入数据
1.1、建表

	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方式

    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 kecheng
    	GROUP BY ID ,NAME

结果与上方一样

2.3、wmsys.wm_concat行列转换函数

    SELECT ID,NAME,wmsys.wm_concat(course || ':'||score) course
    	FROM kecheng
    		GROUP BY ID ,NAME;

2.4、使用over(partition by t.u_id)用法

    SELECT NAME, wmsys.wm_concat(course ||score)  OVER (PARTITION BY NAME)
    	FROM kecheng

三、动态转换

3.1、使用PL/SQL

    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;

temp_ss  表