sqloracle-databaseplsqlunpivot

Unpivot with no-constant expression


I have data like this (notice that my month are in format 'yyyymm'):

car month_1 amount_1 month_2 amount_2
A 202412 100 202501 50
B 202412 200 202501 20
C 202412 300 202501 900

I would like to unpivot my data using PL/SQL. I have tried the following:

select
    car,
    month,
    amount
from TABLE
unpivot exclude nulls
(
    amount for month in
    (
        amount_1 as 202412,
        amount_2 as (select distinct month_2 from TABLE) /* this doesn't work */
    )
);

In order not to change manually each month, I would like to do something like in amount_2, but I get the following error:

ORA-56901: non-constant expression is not allowed for pivot|unpivot values

Although it is a subquery, it is a constant expression (month_2 can only be 202501).

Is there another way to do something like this? Can somebody please help me? Thank you in advance.


Solution

  • You can unpivot multiple columns like below

    UNPIVOT ( (month, amount) 
    FOR col IN ( (month_1, amount_1) as 1 ,(month_2, amount_2) as 2 )) 
    

    See an example in forum

    Sample Query

    SELECT car,month,amount 
    FROM 
      
    (SELECT * FROM t)
    UNPIVOT ( (month, amount) 
    FOR col IN ( (month_1, amount_1) as 1 ,(month_2, amount_2) as 2 )) ;
    

    Output

    CAR MONTH   AMOUNT
    A   202412  100
    A   202501  50
    B   202412  200
    B   202501  20
    C   202412  300
    C   202501  900
    

    Fiddle Demo

    Hope my understanding is correct since expected output is not mentioned.