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

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

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.

• sql
• oracle-database
• plsql
• unpivot

1 Answer 1

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.