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

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