Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with Ordering rows after UNPIVOT columns to rows

Roxy rollersNov 28 2022

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
This post has been answered by mathguy on Nov 28 2022
Jump to Answer

Comments

Post Details

Added on Nov 28 2022
#sql
3 comments
760 views
1 mention