This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,805 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Issue with Ordering rows after UNPIVOT columns to rows

Roxy rollers
Roxy rollers Member Posts: 77 Red Ribbon

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
Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    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).

    Mohamed Houri

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    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.

    Roxy rollers
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    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).

    Mohamed Houri
  • Roxy rollers
    Roxy rollers Member Posts: 77 Red Ribbon

    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