导出列的动态列名 - ORACLE SQL

Question

导出列的动态列名 - ORACLE SQL

3

我想在我的查询中使用 "select from dual" 动态设置列名，这是可能的吗？如果不行，请推荐替代方案以实现此目的。我需要在普通的 select 查询中实现这个功能，而不是使用存储过程。

我想实现以下查询：

SELECT  A.NO
        ,A.SUB_NO
        ,A.DCY
        ,A.STATE

        ,NVL(TO_CHAR(M1.NUMERATOR),'0') AUG_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M1.DENOMINATOR),'0') AUG_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M2.NUMERATOR),'0') JUL_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M2.DENOMINATOR),'0') JUL_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M3.NUMERATOR),'0') JUN_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M3.DENOMINATOR),'0') JUN_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' FROM DUAL

        ,M1.M1_CALC
        ,M2.M2_CALC
        ,M3.M3_CALC

FROM A, M1,M2,M3;

感谢您提前的帮助。

- JCBA

阅读例如立即执行1，2。 - Aksen P

1

问题在于execute immediate需要PL/SQL。 - Acroyear

1

你是否在使用Oracle 18c？虽然你可以通过多态表函数来实现，但我真的想知道这是否值得。如果你在使用18c，我可以为你编写一个示例。否则，就不行了。此外，通常情况下，对于动态命名的列，你需要考虑客户端程序如何消耗/处理结果集，如果他们事先不知道名称的话。通常最好保持SQL结果列静态，并在用户界面中处理重命名。 - Matthew McPeak

@MatthewMcPeak，我在使用Oracle 10G。 - JCBA

@JCBA 如果您有一个调用PL/SQL对象的普通选择查询，这是否足够好？我的开源项目可以在SQL中提供动态SQL，但需要先安装对象。 - Jon Heller

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- d r · Answer 1

WITH A AS
    (
        SELECT  
            636 "NO", 159 "SUB_NO", To_Char(SYSDATE, 'yyyy') "DCY", 'State 1' "STATE"
        FROM dual       --This could be records from any of your data tables
      UNION
        SELECT  
            1272 "NO", 318 "SUB_NO", To_Char(SYSDATE, 'yyyy') "DCY", 'State 2' "STATE"
        FROM dual       --This could be records from any of your data tables
    )
SELECT  
    NO, SUB_NO, DCY, STATE,
    MONTH_MINUS_2_NUM, MONTH_MINUS_2_DEN,
    MONTH_MINUS_3_NUM, MONTH_MINUS_3_DEN,
    MONTH_MINUS_4_NUM, MONTH_MINUS_4_DEN,
    M1_CALC, M2_CALC, M3_CALC
FROM 
    a
MODEL
    DIMENSION BY( DCY, STATE)
    MEASURES( NO, SUB_NO,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_2_NUM, 
              CAST('x' as VarChar2(20)) as MONTH_MINUS_2_DEN,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_3_NUM,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_3_DEN,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_4_NUM,
              CAST('x' as VarChar2(20)) as MONTH_MINUS_4_DEN,
              0 as M1_CALC,
              0 as M2_CALC,
              0 as M3_CALC
             )
    RULES
    (
      MONTH_MINUS_2_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM',
      MONTH_MINUS_2_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN',
      MONTH_MINUS_3_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM',
      MONTH_MINUS_3_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN',
      MONTH_MINUS_4_NUM[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM',
      MONTH_MINUS_4_DEN[ANY, ANY] = TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN',
  -- Dynamic Calculations -> you can calculate prety much anything you want
      M1_CALC[2022, 'State 1'] = NO[CV(), CV()] / 2,
      M2_CALC[2022, 'State 1'] = NO[CV(), CV()] / 3,
      M3_CALC[2022, 'State 1'] = NO[CV(), CV()] / 4,
      M1_CALC[2022, 'State 2'] = NO[CV(), CV()] / 2,
      M2_CALC[2022, 'State 2'] = NO[CV(), CV()] / 3,
      M3_CALC[2022, 'State 2'] = NO[CV(), CV()] / 4
    )

- Acroyear · Answer 2

我相信你想要的最接近的方法就是使用 UNION ALL。

SELECT NULL no,
       NULL sub_no,
       NULL dcy,
       NULL state,
       TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' AUG_NUM,
       TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' AUG_DEN,
       TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' JUL_NUM,
       TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' JUL_DEN,
       TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' JUN_NUM,
       TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' JUN_DEN,
       NULL M1_CAL,
       NULL M2_CALC,
       NULL M3_CALC
FROM dual
UNION ALL
SELECT  A.NO
        ,A.SUB_NO
        ,A.DCY
        ,A.STATE

        ,NVL(TO_CHAR(M1.NUMERATOR),'0') AUG_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M1.DENOMINATOR),'0') AUG_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M2.NUMERATOR),'0') JUL_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M2.DENOMINATOR),'0') JUL_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-3),'MON')||'_DEN' FROM DUAL

        ,NVL(TO_CHAR(M3.NUMERATOR),'0') JUN_NUM     --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_NUM' FROM DUAL
        ,NVL(TO_CHAR(M3.DENOMINATOR),'0') JUN_DEN   --AS SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-4),'MON')||'_DEN' FROM DUAL

        ,M1.M1_CALC
        ,M2.M2_CALC
        ,M3.M3_CALC
FROM A, M1,M2,M3;