Issue with Ordering rows after UNPIVOT columns to rows

Roxy rollers

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

mathguy

In this case, it's best to add an ordering column right as you unpivot. Then you don't need to do any extra work after the fact.

select rep_month, type, value
from dummy_unpivot
  unpivot
  (
    value
    for (type, seq) in
      (
       col_1 AS ('Category: <100'   , 1),
       col_2 AS ('Category: <200'   , 2),
       col_3 AS ('Category: 200-349', 3),
       col_4 as ('Category: 350-500', 4),
       col_5 AS ('Category: >500'   , 5)
      )
  )
order by rep_month, seq;

Notice the change in the FOR sub-clause: for each column in the input you are generating two columns in the output, one for "type" and the other for ordering. The values (1, 2, 3, 4, 5) are used only for ordering. By the way, if you want a different order, just change those numbers.

If you don't want SEQ in the output, then don't select * in the query - select just the columns you need.

If you are curious - the order you get with your query is not random. The TYPE strings are ordered alphabetically; numbers come before the "strictly less than" and "strictly greater than" in alphabetical order (at least in ASCII, which is incorporated in almost all character sets).

Frank Kulash

Hi, @Roxy rollers

My ordering right now seems to be random as seen below.

Category: 200-349
Category: 350-500
Category: <100
Category: <200
Category: >500

You only get 5 rows and 1 column? When I run the query you posted, I get 15 rows and 3 columns, like this:

REP_MONTH   TYPE               VALUE
----------- ----------------- ------
01-Jun-2022 Category: 200-349    113
01-Jun-2022 Category: 350-500    199
01-Jun-2022 Category: <100        16
01-Jun-2022 Category: <200        73
01-Jun-2022 Category: >500       797
01-Jul-2022 Category: 200-349    109
01-Jul-2022 Category: 350-500    191
01-Jul-2022 Category: <100        14
01-Jul-2022 Category: <200        60
01-Jul-2022 Category: >500       767
01-Aug-2022 Category: 200-349    131
01-Aug-2022 Category: 350-500    201
01-Aug-2022 Category: <100        15
01-Aug-2022 Category: <200        71
01-Aug-2022 Category: >500       793

Fir the output do you want only the 5 rows and 1 column you posted? If not, post the exact results you want from the given sample data. Always post the exact results you want from the given sample data.

mathguy

In this case, it's best to add an ordering column right as you unpivot. Then you don't need to do any extra work after the fact.

select rep_month, type, value
from dummy_unpivot
  unpivot
  (
    value
    for (type, seq) in
      (
       col_1 AS ('Category: <100'   , 1),
       col_2 AS ('Category: <200'   , 2),
       col_3 AS ('Category: 200-349', 3),
       col_4 as ('Category: 350-500', 4),
       col_5 AS ('Category: >500'   , 5)
      )
  )
order by rep_month, seq;

Notice the change in the FOR sub-clause: for each column in the input you are generating two columns in the output, one for "type" and the other for ordering. The values (1, 2, 3, 4, 5) are used only for ordering. By the way, if you want a different order, just change those numbers.

If you don't want SEQ in the output, then don't select * in the query - select just the columns you need.

If you are curious - the order you get with your query is not random. The TYPE strings are ordered alphabetically; numbers come before the "strictly less than" and "strictly greater than" in alphabetical order (at least in ASCII, which is incorporated in almost all character sets).

Roxy rollers

Apologies everyone. Yes, my outputs is as follows:

REP_MONTH               TYPE                    VALUE
01-JUN-2022 00:00:00	Category: 200-349	113
01-JUN-2022 00:00:00	Category: 350-500	199
01-JUN-2022 00:00:00	Category: <100	         16
01-JUN-2022 00:00:00	Category: <200	         73
01-JUN-2022 00:00:00	Category:  >500	        797
01-JUL-2022 00:00:00	Category: 200-349	109
01-JUL-2022 00:00:00	Category: 350-500	191
01-JUL-2022 00:00:00	Category: <100	         14
01-JUL-2022 00:00:00	Category: <200	         60
01-JUL-2022 00:00:00	Category: >500	        767
01-AUG-2022 00:00:00	Category: 200-349	131
01-AUG-2022 00:00:00	Category: 350-500	201
01-AUG-2022 00:00:00	Category: <100	         15
01-AUG-2022 00:00:00	Category: <200	         71
01-AUG-2022 00:00:00	Category: >500	        793