Oracle的行列转换

[b]行转列：[/b]
1.列固定的情况，通过max+decode变换。

WITH t AS (
    SELECT 1 tid,'A' typeid, 'book1'  typename FROM DUAL UNION ALL
    SELECT 1 tid,'B' typeid, 'apple1' typename FROM DUAL UNION ALL
    SELECT 1 tid,'C' typeid, 'phone1' typename FROM DUAL UNION ALL
    SELECT 1 tid,'D' typeid, 'eye1'   typename FROM DUAL UNION ALL
    SELECT 2 tid,'A' typeid, 'book2'  typename FROM DUAL UNION ALL
    SELECT 2 tid,'B' typeid, 'apple2' typename FROM DUAL UNION ALL
    SELECT 2 tid,'C' typeid, 'phone2' typename FROM DUAL UNION ALL
    SELECT 3 tid,'B' typeid, 'apple3' typename FROM DUAL UNION ALL
    SELECT 3 tid,'C' typeid, 'phone3' typename FROM DUAL
)
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      phone3

SELECT 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 DUAL

TID    A        B         C         D
1      book1    apple1    phone1    eye1
2      book2    apple2    phone2    
3      apple3   phone3    

3.通过层次查询，将行转换成字符串。

SELECT m.tid,
       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
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH m.rn = 1
CONNECT BY PRIOR m.rn = m.rn - 1
       AND PRIOR m.tid = m.tid;

TID TYPENAME
------- --------------------------------------------------------------------------------
1 book1,apple1,phone1,eye1
2 book2,apple2,phone2
3 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

[b]列转行：[/b]
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      book1
1   B      apple1
1   C      phone1
1   D      eye1
2   A      book2
2   B      apple2
2   C      phone2
3   B      apple3
3   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 book1
1            2 apple1
1            3 phone1
1            4 eye1
2            1 book2
2            2 apple2
2            3 phone2
3            1 apple3
3            2 phone3