[Oracle] Oracle实现行列转换的方法分析

student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……

语文 数学 英语
student1 80 70 60
student2 90 80 100
……

select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;

c1 c2
--- -----------
1 我
1 是
1 谁
2 知
2 道
3 不
……

1 我是谁
2 知道
3 不

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
select distinct c1 ,get_c2(c1) cc2 from table;

SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAME
  FROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1
    FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn
      FROM t))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY c1;

CLASS1   CALLDATE   CALLCOUNT
1   2005-08-08  40
1   2005-08-07  6
2   2005-08-08  77
3   2005-08-09  33
3   2005-08-08  9
3   2005-08-07  21

CALLDATE   CallCount1 CallCount2 CallCount3
------------ ---------- ---------- ----------
2005-08-09  0   0   33
2005-08-08  40   77   9
2005-08-07 6 0   21

CREATE TABLE t(
  class1 VARCHAR2(2 BYTE),
  calldate DATE,
  callcount INTEGER
);
INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);
INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);
INSERT INTO t(class1, calldate, callcount)
VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);
INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);
INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);
INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);
COMMIT ;

CREATE OR REPLACE PACKAGE pkg_getrecord
IS
  TYPE myrctype IS REF CURSOR;
END pkg_getrecord;

CREATE OR REPLACE FUNCTION fn_rs
  RETURN pkg_getrecord.myrctype
IS
  s VARCHAR2 (4000);
  CURSOR c1 IS
  SELECT ',sum(case when Class1='
    || class1
    || ' then CallCount else 0 end)'
    || ' "CallCount'
    || class1
    || '"' c2
  FROM t
  GROUP BY class1;
  r1 c1%ROWTYPE;
  list_cursor pkg_getrecord.myrctype;
BEGIN
  s := 'select CallDate ';
  OPEN c1;
  LOOP
  FETCH c1 INTO r1;
  EXIT WHEN c1%NOTFOUND;
  s := s || r1.c2;
  END LOOP;
  CLOSE c1;
  s := s || ' from T group by CallDate order by CallDate desc ';
  OPEN list_cursor FOR s;
  RETURN list_cursor;
END fn_rs;

var results refcursor;
exec :results := fn_rs;
print results;
CALLDATE  CallCount1 CallCount2 CallCount3
--------------- ---------- ---------- ----------
2005-08-09   0   0   33
2005-08-08   40   77   9
2005-08-07   6   0   21