Oracle 动态行转列

思路：

1）创建PROC拼接SQL语句；

2）创建VIEW，查询视图返回结果集。

CREATE OR REPLACE PROCEDURE JIT_P_EDI_ORG_BOM
AUTHID CURRENT_USER IS
BEGIN
DECLARE MAX_CNT INT := 0; --相同版本号成品对应料件数量
ROW_INDEX INT := 1; --循环索引
V_SQL VARCHAR2(8000);

BEGIN

SELECT MAX(CNT) INTO MAX_CNT FROM (SELECT EXG_NO,IMG_NO,COUNT(1) CNT FROM EMS_EDI_ORG_BOM GROUP BY EXG_NO,IMG_NO) T;

V_SQL := 'SELECT EXG_NO AS 成品序号,IMG_NO AS 料件序号';

WHILE (ROW_INDEX <= MAX_CNT)
LOOP
V_SQL := V_SQL || ',' || 'MAX(DECODE(RN,''' || ROW_INDEX ||
''',BEGIN_DATE,NULL)) AS 版本号' || ROW_INDEX
|| ',' || 'MAX(DECODE(RN,''' || ROW_INDEX ||
''',DEC_CM,NULL)) AS 单耗' || ROW_INDEX ;

ROW_INDEX := ROW_INDEX + 1;
END LOOP;

V_SQL := V_SQL || ' FROM (SELECT EXG_NO,IMG_NO,BEGIN_DATE,DEC_CM,ROW_NUMBER() OVER(PARTITION BY EXG_NO,IMG_NO ORDER BY EXG_NO, IMG_NO) RN
FROM EMS_EDI_ORG_BOM) GROUP BY EXG_NO,IMG_NO';

V_SQL := 'CREATE OR REPLACE VIEW JIT_V_ORG_BOM AS '|| V_SQL;

EXECUTE IMMEDIATE V_SQL;

END;
COMMIT;
--异常处理
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;

SELECT V.*,COUNT(1) OVER(PARTITION BY 1) AS All_ROWS FROM JIT_V_ORG_BOM V