Oracle 将行转为列（即行转列）有多种方法

CREATE OR REPLACE PROCEDURE sp_pivot_scores (
    p_cursor OUT SYS_REFCURSOR
) IS
    v_sql CLOB;
    v_columns VARCHAR2(4000);
BEGIN
    -- 1. 动态获取所有科目，拼接成列名列表
    SELECT LISTAGG('''' || subject || ''' AS "' || subject || '"', ', ') 
           WITHIN GROUP (ORDER BY subject)
    INTO v_columns
    FROM (SELECT DISTINCT subject FROM scores);

    -- 2. 拼接完整的 PIVOT SQL
    v_sql := 'SELECT * 
              FROM scores
              PIVOT (
                  MAX(result) 
                  FOR subject IN (' || v_columns || ')
              )
              ORDER BY 姓名';

    -- 3. 打开游标返回结果
    OPEN p_cursor FOR v_sql;
END;
/

调用方式（在 SQL*Plus / SQL Developer 中）：

VARIABLE cur REFCURSOR;
EXEC sp_pivot_scores(:cur);
PRINT cur;

CREATE OR REPLACE PROCEDURE sp_pivot_scores_case (
    p_cursor OUT SYS_REFCURSOR
) IS
    v_sql CLOB;
    v_case_part VARCHAR2(4000);
BEGIN
    -- 1. 动态生成 CASE WHEN 片段
    SELECT LISTAGG(
               'MAX(CASE WHEN subject = ''' || subject || ''' THEN result END) AS "' || subject || '"',
               ', '
           ) WITHIN GROUP (ORDER BY subject)
    INTO v_case_part
    FROM (SELECT DISTINCT subject FROM scores);

    -- 2. 拼接完整 SQL
    v_sql := 'SELECT 姓名, ' || v_case_part || ' 
              FROM scores 
              GROUP BY 姓名 
              ORDER BY 姓名';

    -- 3. 打开游标
    OPEN p_cursor FOR v_sql;
END;
/

CREATE OR REPLACE PROCEDURE sp_create_pivot_view IS
    v_sql CLOB;
    v_columns VARCHAR2(4000);
BEGIN
    -- 获取所有科目
    SELECT LISTAGG('''' || subject || ''' AS "' || subject || '"', ', ') 
           WITHIN GROUP (ORDER BY subject)
    INTO v_columns
    FROM (SELECT DISTINCT subject FROM scores);

    -- 创建或替换视图
    v_sql := 'CREATE OR REPLACE VIEW v_pivot_scores AS
              SELECT * 
              FROM scores
              PIVOT (
                  MAX(result) 
                  FOR subject IN (' || v_columns || ')
              )';

    EXECUTE IMMEDIATE v_sql;
    
    DBMS_OUTPUT.PUT_LINE('视图 v_pivot_scores 创建成功！');
END;
/

EXEC sp_create_pivot_view;
-- 然后直接查询视图
SELECT * FROM v_pivot_scores;

-- 1. 先定义一个对象类型和嵌套表类型
CREATE OR REPLACE TYPE pivot_row_obj AS OBJECT (
    姓名 VARCHAR2(20),
    语文 VARCHAR2(10),
    数学 VARCHAR2(10),
    外语 VARCHAR2(10)
);
/

CREATE OR REPLACE TYPE pivot_table_type AS TABLE OF pivot_row_obj;
/

-- 2. 创建管道函数
CREATE OR REPLACE FUNCTION fn_pivot_scores 
RETURN pivot_table_type PIPELINED IS
    v_sql CLOB;
    v_columns VARCHAR2(4000);
    v_cursor SYS_REFCURSOR;
    
    v_name VARCHAR2(20);
    v_subject VARCHAR2(10);
    v_result VARCHAR2(10);
    
    -- 收集所有科目（动态）
    TYPE subject_list IS TABLE OF VARCHAR2(20);
    v_subjects subject_list := subject_list();
    
    -- 存储每行数据
    TYPE row_data IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(20);
    v_row row_data;
    v_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
    v_processed_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
    
    v_found BOOLEAN;
BEGIN
    -- 这个方法较复杂，不推荐用于生产环境
    -- 建议使用前面的 REF CURSOR 方式
    NULL;
END;
/

-- 1. 创建表和插入数据（如果还没有）
CREATE TABLE scores (name VARCHAR2(20), subject VARCHAR2(20), result VARCHAR2(10));

INSERT INTO scores VALUES ('张三', '语文', '合格');
INSERT INTO scores VALUES ('张三', '数学', '不合格');
INSERT INTO scores VALUES ('张三', '外语', '合格');
INSERT INTO scores VALUES ('李四', '语文', '合格');
INSERT INTO scores VALUES ('李四', '数学', '合格');
INSERT INTO scores VALUES ('李四', '外语', '合格');
INSERT INTO scores VALUES ('王五', '语文', '合格');
INSERT INTO scores VALUES ('王五', '数学', '合格');
INSERT INTO scores VALUES ('王五', '外语', '合格');
COMMIT;

-- 2. 创建存储过程（方式一）
CREATE OR REPLACE PROCEDURE sp_pivot_scores (
    p_cursor OUT SYS_REFCURSOR
) IS
    v_sql CLOB;
    v_columns VARCHAR2(4000);
BEGIN
    SELECT LISTAGG('''' || subject || ''' AS "' || subject || '"', ', ') 
           WITHIN GROUP (ORDER BY subject)
    INTO v_columns
    FROM (SELECT DISTINCT subject FROM scores);

    v_sql := 'SELECT * 
              FROM scores
              PIVOT (
                  MAX(result) 
                  FOR subject IN (' || v_columns || ')
              )
              ORDER BY 姓名';

    OPEN p_cursor FOR v_sql;
END;
/

-- 3. 调用测试
VARIABLE cur REFCURSOR;
EXEC sp_pivot_scores(:cur);
PRINT cur;