Pivot Issue ORA-00918 column ambiguously defined-CodePudding

I am running the below query and need to output the rows as columns. When I am adding the second MAX statement, I am getting an error ORA-00918 column ambiguously defined. Not sure what I am doing wrong. Any help will be appreciated.

SELECT * from ( 
SELECT a.REF_NUM as "Number", a.SUMMARY, a.DESC_SEARCH as "Description", a.Status, e.Label, e.Value
        FROM ca a,
        cr_prp e
    WHERE 
        a.PERSID = e.OWNING_CR
        AND a.CATEGORY = '16996807'
        ORDER by a.REF_NUM DESC)t
    PIVOT
    (
    MAX(CASE WHEN LABEL = 'Plan/UnPlanned' THEN Value END),
    MAX(CASE WHEN LABEL = 'Reason for' THEN Value END),
    MAX(CASE WHEN LABEL = 'Name & "ID"' THEN Value END)
    FOR LABEL
    IN ('Plan/UnPlanned',
        'Reason for',
        'Name & "ID"')
    )

CodePudding user response：

You probably need something like this

select * from tab
    PIVOT
    (
    MAX(Value)
    FOR LABEL
    IN ('Plan/UnPlanned' as PU,
        'Reason for' as R,
        'Name & "ID"' as NI)
 )  


    Number    SUMMARY D     STATUS         PU          R         NI
---------- ---------- - ---------- ---------- ---------- ----------
         2          2 z          2                                1
         1          1 x          1          2                      
         2          2 y          2                     1

I.e. 1) add alias to you pivot labels

do not multiplicate the MAX calculation, it it the responsibility of PIVOT to calculate max for each label.