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.
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
Hope my understanding is correct since expected output is not mentioned.