Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Issue with Ordering rows after UNPIVOT columns to rows

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