Hi folks,
I tried a few examples I saw online but am having difficulty doing the unpivot. I am including table creation scripts and insert statements. I am including my SQL as well. Thank you in advance for any help in this matter.
CREATE TABLE DUMMY_UNPIVOT
( REP_MONTH DATE,
COL_1 NUMBER,
COL_2 NUMBER,
COL_3 NUMBER,
COL_4 NUMBER,
COL_5 NUMBER
);
insert into DUMMY_UNPIVOT (REP_MONTH,COL_1,COL_2,COL_3,COL_4,COL_5) values (to_date('01-JUN-2022','DD-MON-YYYY'),16,73,113,199,797);
insert into DUMMY_UNPIVOT (REP_MONTH,COL_1,COL_2,COL_3,COL_4,COL_5) values (to_date('01-JUL-2022','DD-MON-YYYY'),14,60,109,191,767);
insert into DUMMY_UNPIVOT (REP_MONTH,COL_1,COL_2,COL_3,COL_4,COL_5) values (to_date('01-AUG-2022','DD-MON-YYYY'),15,71,131,201,793);
My SQL Statement is:
select * from dummy_unpivot
unpivot
(
value
for type in
(
col_1 AS 'Category: <100',
col_2 AS 'Category: <200',
col_3 AS 'Category: 200-349',
col_4 as 'Category: 350-500',
col_5 AS 'Category: >500'
)
)
order by rep_month, type;
My ordering right now seems to be random as seen below.
Category: 200-349
Category: 350-500
Category: <100
Category: <200
Category: >500
My ordering should be instead as seen below. I believe I have to UNPIVOT multiple columns and perhaps one of them should be a number column that would do my ordering based on but I am unsure how to proceed.
Thanks!
Category: <100
Category: <200
Category: 200-349
Category: 350-500
Category: >500