Oracle SQL Columns to Rows without UNPIVOT

Question

What I currently have:

Team    User    Apples    Oranges    Pears
Red     Adam    4         5          6
Red     Avril   11        12         13
Blue    David   21        22         23

What's needed:

Team    User    Product    Count
Red     Adam    Apples     4
Red     Adam    Oranges    5
Red     Adam    Pears      6
Red     Avril   Apples     11
Red     Avril   Oranges    12
Red     Avril   Pears      13
Blue    David   Apples     21
....

This is to be implemented using Oracle SQL. I understand this can be done using UNPIVOT, but my Oracle SQL version is too old to support this method. Can someone provide an example of how to achieve this using CROSS APPLY or equivalent methods? Count changes depending on team-user-product combination, and the number of product types may change slightly in the future so a scalable solution might be necessary.

This is time-sensitive, so I appreciate the help.

You could do this using a series of unions, but that would be fairly ugly. Have you tried something yet? — Tim Biegeleisen, Jan 25 '17 at 07:37
`select team, "USER", apples as product from the_table union all select team, "USER", oranges from the_table ...` — , Jan 25 '17 at 07:37

Boneist · Accepted Answer · 2017-01-25T09:13:33.520

You can do this using a cross join and some case statements by using a dummy subquery that holds the same number of rows as you have columns that you want to unpivot (since you want each column to go into its own row) like so:

WITH your_table AS (SELECT 'Red' Team, 'Adam' usr, 4 Apples, 5 Oranges, 6 Pears FROM dual UNION ALL
                    SELECT 'Red' Team, 'Avril' usr, 11 Apples, 12 Oranges, 13 Pears FROM dual UNION ALL
                    SELECT 'Blue' Team, 'David' usr, 21 Apples, 22 Oranges, 23 Pears FROM dual)
-- end of mimicking your table. See SQL below:
SELECT yt.team,
       yt.usr,
       CASE WHEN d.id = 1 THEN 'Apples'
            WHEN d.id = 2 THEN 'Oranges'
            WHEN d.id = 3 THEN 'Pears'
       END product,
       CASE WHEN d.id = 1 THEN yt.apples
            WHEN d.id = 2 THEN yt.oranges
            WHEN d.id = 3 THEN yt.pears
       END count_of_product
FROM   your_table yt
       CROSS JOIN (SELECT LEVEL ID
                   FROM   dual
                   CONNECT BY LEVEL <= 3) d -- number of columns to unpivot
ORDER BY team, usr, product;

TEAM USR   PRODUCT COUNT_OF_PRODUCT
---- ----- ------- ----------------
Blue David Apples                21
Blue David Oranges               22
Blue David Pears                 23
Red  Adam  Apples                 4
Red  Adam  Oranges                5
Red  Adam  Pears                  6
Red  Avril Apples                11
Red  Avril Oranges               12
Red  Avril Pears                 13

Doing it this way means that you only have to go through the table once, rather than multiple times if you were doing the union all method.

ETA: Here's the method that Aleksej was referring to - I would suggest testing both methods against your set of data (which is hopefully large enough to be representative) to see which one is more performant:

WITH your_table AS (SELECT 'Red' Team, 'Adam' usr, 4 Apples, 5 Oranges, 6 Pears FROM dual UNION ALL
                    SELECT 'Red' Team, 'Avril' usr, 11 Apples, 12 Oranges, 13 Pears FROM dual UNION ALL
                    SELECT 'Blue' Team, 'David' usr, 21 Apples, 22 Oranges, 23 Pears FROM dual)
-- end of mimicking your table. See SQL below:
SELECT yt.team,
       yt.usr,
       CASE WHEN LEVEL = 1 THEN 'Apples'
            WHEN LEVEL = 2 THEN 'Oranges'
            WHEN LEVEL = 3 THEN 'Pears'
       END product,
       CASE WHEN LEVEL = 1 THEN yt.apples
            WHEN LEVEL = 2 THEN yt.oranges
            WHEN LEVEL = 3 THEN yt.pears
       END count_of_product
FROM   your_table yt
CONNECT BY PRIOR team = team
           AND PRIOR usr = usr
           AND PRIOR sys_guid() IS NOT NULL
           AND LEVEL <= 3
ORDER BY team, usr, product;

TEAM USR   PRODUCT COUNT_OF_PRODUCT
---- ----- ------- ----------------
Blue David Apples                21
Blue David Oranges               22
Blue David Pears                 23
Red  Adam  Apples                 4
Red  Adam  Oranges                5
Red  Adam  Pears                  6
Red  Avril Apples                11
Red  Avril Oranges               12
Red  Avril Pears                 13

I like this approach; I believe you could even simplify it by removing the `JOIN` and applying the `CONNECT BY` directly on the source table, with no need for `DUAL` — Aleksej, Jan 25 '17 at 08:25
@Aleksej I hadn't considered that; it's possible, but I don't know if it would be as performant. I don't have time atm to test it out, unfortunately. — Boneist, Jan 25 '17 at 08:48
This approach has solved my problem and shortened a significant amount of man-hours. However I'd like to know more baout Aleksej's idea, can you provide more details? — Toon, Jan 25 '17 at 09:09
The 2nd solution introduces too many AND (in fact nearly 10 of them) clauses after CONNECT BY, resulting in a messy code. The data set I have has a large number of columns and cross joining therefore the 1st solution is preferred. — Toon, Jan 25 '17 at 09:31
[This question](http://stackoverflow.com/q/38371989/1509264) includes a discussion of different methods of generating multiple rows from a single row and their relative performance. The conclusion was that using `CONNECT BY` with a `SYS_GUID()` hack directly on the source table was less performant than joining to a hierarchically generated table (or using a recursive sub-query factoring clause - a.k.a. a CTE) to generate the rows. However, you should profile different solutions to find which is best for your database. — MT0, Jan 25 '17 at 09:36
You only need enough `AND prior = ` predicates in order to uniquely identify the row. In your example, that happened to be the team and usr columns, but if you are selecting the primary key column(s) then those are the columns that need to be in the connect by. — Boneist, Jan 25 '17 at 09:36

score 1 · Answer 2 · answered Jan 25 '17 at 07:52

You can use a big union all like this:

select 
    Team,
    "User",
    'Apples' Product,
    Apples "Count"
from your_table
union all
select 
    Team,
    "User",
    'Oranges' Product,
    Oranges "Count"
from your_table
union all
select 
    Team,
    "User",
    'Pears' Product,
    Pears "Count"
from your_table
union all
. . .

Also, try not to use keywords such as User or Count as identifiers or else, wrap them in double quotes like I did.

Oracle SQL Columns to Rows without UNPIVOT

2 Answers2

Linked