Oracle Database：合并列实现Pivot并解决ORA-00918错误

Let's break down the issues and fix your query step by step:

1. Why You're Getting ORA-00918 #

The immediate error comes from your subquery: you've included EMPL_FMONTH twice in the SELECT list. Oracle can't distinguish between duplicate column names, hence the "ambiguously defined" error. We'll start by removing that duplicate column.

2. Corrected Query for MM/YYYY Column Headers #

To get the desired MM/YYYY formatted column headers, you need to assign explicit aliases to each (EMPL_FMONTH, EMPL_FYEAR) pair in the PIVOT's IN clause. Use double quotes around aliases that include special characters like /.

SELECT 
    ORG_TOT_LOWEST_LEVEL_ID,
    ORG_TOT_ACCTG_DEPT_ID,
    EMPL_SCENR_DIM_MBR_CD,
    EMPL_VER_DIM_MBR_CD,
    ORG_TOT_ALLOC_POOL_CD,
    "1/2018",
    "6/2019"
FROM ( 
    SELECT 
        ORG_TOT_LOWEST_LEVEL_ID,
        ORG_TOT_ACCTG_DEPT_ID,
        EMPL_SCENR_DIM_MBR_CD,
        EMPL_VER_DIM_MBR_CD,
        ORG_TOT_ALLOC_POOL_CD,
        EMPL_VAL_AMT,
        EMPL_FMONTH,
        EMPL_FYEAR
    FROM EMPL_FCST 
) 
PIVOT ( 
    SUM(EMPL_VAL_AMT) 
    FOR (EMPL_FMONTH, EMPL_FYEAR) IN (
        (1, 2018) AS "1/2018",
        (6, 2019) AS "6/2019"
    )
);

Key Adjustments Explained #

• Removed duplicate EMPL_FMONTH: Fixes the ORA-00918 error immediately.
• Explicit column selection: Avoid using SELECT * after PIVOT—listing columns explicitly gives you control over the output and avoids unexpected columns.
• Aliased PIVOT pairs: Each (month, year) combination is assigned an alias in MM/YYYY format, wrapped in double quotes to handle the / character. This ensures your column headers match the expected format.

Note for String-Type Months #

If EMPL_FMONTH is stored as a string (e.g., '01' instead of numeric 1), adjust the IN clause to use string literals:

FOR (EMPL_FMONTH, EMPL_FYEAR) IN (
    ('01', '2018') AS "01/2018",
    ('06', '2019') AS "06/2019"
)

内容的提问来源于stack exchange，提问作者user9778160