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.

Answer 1

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

Answer 2

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.