Oracle的行列转换
1.列固定的情况，通过max+decode变换。
SQL> WITH t AS ( 2 SELECT 1 tid,'A' typeid, 'book1' typename FROM DUAL UNION ALL 3 SELECT 1 tid,'B' typeid, 'apple1' typename FROM DUAL UNION ALL 4 SELECT 1 tid,'C' typeid, 'phone1' typename FROM DUAL UNION ALL 5 SELECT 1 tid,'D' typeid, 'eye1' typename FROM DUAL UNION ALL 6 SELECT 2 tid,'A' typeid, 'book2' typename FROM DUAL UNION ALL 7 SELECT 2 tid,'B' typeid, 'apple2' typename FROM DUAL UNION ALL 8 SELECT 2 tid,'C' typeid, 'phone2' typename FROM DUAL UNION ALL 9 SELECT 3 tid,'B' typeid, 'apple3' typename FROM DUAL UNION ALL 10 SELECT 3 tid,'C' typeid, 'phone3' typename FROM DUAL 11 ) 12 SELECT * FROM t; TID TYPEID TYPENAME---------- ------ -------- 1 A book1 1 B apple1 1 C phone1 1 D eye1 2 A book2 2 B apple2 2 C phone2 3 B apple3 3 C phone3SELECT t.tid, MAX(DECODE(t.typeid,'A',t.typename)) A, MAX(DECODE(t.typeid,'B',t.typename)) B, MAX(DECODE(t.typeid,'C',t.typename)) C, MAX(DECODE(t.typeid,'D',t.typename)) D FROM t GROUP BY t.tid TID A B C D---------- ------ ------ ------ ------ 1 book1 apple1 phone1 eye1 2 book2 apple2 phone2 3 apple3 phone3
2.列不固定的时候，通过自定义function转换。
CREATE OR REPLACE PACKAGE util IS TYPE CURSOR_TYPE IS REF CURSOR; FUNCTION ROW_TO_COL(table_name VARCHAR2, group_key VARCHAR2, col_key VARCHAR2, operation_symbol VARCHAR2, calc_col VARCHAR2, order_key VARCHAR2) RETURN CURSOR_TYPE;END util;CREATE OR REPLACE PACKAGE BODY util IS FUNCTION ROW_TO_COL(table_name VARCHAR2, group_key VARCHAR2, col_key VARCHAR2, operation_symbol VARCHAR2, calc_col VARCHAR2, order_key VARCHAR2) RETURN CURSOR_TYPE IS cur CURSOR_TYPE; TYPE arrays IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; column_array arrays; strSql VARCHAR2(500); BEGIN strSql := ' SELECT DISTINCT ' || col_key || ' FROM ' || table_name || ' ORDER BY ' || col_key; OPEN cur FOR strSql; FETCH cur BULK COLLECT INTO column_array; CLOSE cur; strSql := 'SELECT '; IF group_key IS NOT NULL THEN strSql := strSql || group_key || ','; END IF; FOR i IN column_array.FIRST .. column_array.LAST LOOP strSql := strSql || operation_symbol || '(DECODE(' || col_key || ',''' || column_array(i) || ''',' || calc_col || ',NULL)) ' || column_array(i); IF i < column_array.LAST THEN strSql := strSql || ','; END IF; END LOOP; strSql := strSql || ' FROM ' || table_name; IF group_key IS NOT NULL THEN strSql := strSql || ' GROUP BY ' || group_key; END IF; IF order_key IS NOT NULL THEN strSql := strSql || ' ORDER BY ' || order_key; END IF; OPEN cur FOR strSql; RETURN cur; EXCEPTION WHEN OTHERS THEN IF cur%ISOPEN THEN CLOSE cur; END IF; RAISE; END ROW_TO_COL;END util;--调用方式，函数返回一个游标，通过plsql/developer可以查看。SELECT util.ROW_TO_COL('tb','tid','typeid','max','typename','tid') FROM DUALTID A B C D1 book1 apple1 phone1 eye12 book2 apple2 phone2 3 apple3 phone3 3.通过层次查询。
SELECT m.tid, SUBSTR(SYS_CONNECT_BY_PATH(m.typename, ','), 2) typenameFROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.tid ORDER BY t.typeid) rn FROM t) mWHERE CONNECT_BY_ISLEAF = 1START WITH m.rn = 1CONNECT BY PRIOR m.rn = m.rn - 1 AND PRIOR m.tid = m.tid;TID TYPENAME------- --------------------------------------------1 book1,apple1,phone1,eye12 book2,apple2,phone23 apple3,phone3
oracle 9i中没有connect_by_isleaf,可以使用下面的
SELECT n.tid, MAX(n.typename) KEEP(DENSE_RANK LAST ORDER BY n.rn) typename FROM (SELECT m.tid, m.rn, SUBSTR(SYS_CONNECT_BY_PATH(m.typename, ','), 2) typename FROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.tid ORDER BY t.typeid) rn FROM t) m START WITH m.rn = 1 CONNECT BY PRIOR m.rn = m.rn - 1 AND PRIOR m.tid = m.tid) n GROUP BY n.tid
列转行
1.union all。
SQL> WITH t AS ( 2 SELECT '1' tid,'book1' A,'apple1' B,'phone1' C,'eye1' D FROM DUAL UNION ALL 3 SELECT '2' tid,'book2' A,'apple2' B,'phone2' C,NULL D FROM DUAL UNION ALL 4 SELECT '3' tid,NULL A,'apple3' B,'phone3' C,NULL D FROM DUAL 5 ) 6 SELECT * FROM ( 7 SELECT t.tid,'A' typeid, t.a typename FROM t UNION ALL 8 SELECT t.tid,'B' typeid, t.b typename FROM t UNION ALL 9 SELECT t.tid,'C' typeid, t.c typename FROM t UNION ALL 10 SELECT t.tid,'D' typeid, t.d typename FROM t 11 ) m 12 WHERE m.typename IS NOT NULL 13 ORDER BY m.tid,m.typeid 14 ;TID TYPEID TYPENAME--- ------ --------1 A book11 B apple11 C phone11 D eye12 A book22 B apple22 C phone23 B apple33 C phone3
2.字符串转列
SQL> WITH t AS ( 2 SELECT '1' tid,'book1,apple1,phone1,eye1' typename FROM DUAL UNION ALL 3 SELECT '2' tid,'book2,apple2,phone2' typename FROM DUAL UNION ALL 4 SELECT '3' tid,'apple3,phone3' typename FROM DUAL 5 ) 6 SELECT tid, 7 LEVEL AS lev, 8 RTRIM(REGEXP_SUBSTR(typename || ',', '.*?' || ',', 1, LEVEL), ',') AS typename 9 FROM t 10 CONNECT BY tid = connect_by_root tid 11 AND LEVEL <= 12 LENGTH(REGEXP_REPLACE(typename || ',', '[^' || ',' || ']', NULL)) 13 ORDER BY 1,2;TID LEV TYPENAME--- ---------- --------------------------------------------1 1 book11 2 apple11 3 phone11 4 eye12 1 book22 2 apple22 3 phone23 1 apple33 2 phone3