select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;
2、不定列行列转换
如:
c1 c2
--- -----------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换可以借助于PL/SQL来完成,这里给一个例子
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;
或者不用pl/sql,利用分析函数和 CONNECT_BY 实现:
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;
CREATE OR REPLACE PACKAGE pkg_getrecord
IS
TYPE myrctype IS REF CURSOR;
END pkg_getrecord;
3). 建立动态sql交叉表函数,输出结果集
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;