Convert columns to rows in oracle 9i

Question

Please help me to the SQL Oracle 9i, convert columns to rows in group by ID.
I can't convert using UNPIVOT in oracle 9i. And I will not use UNION ALL, it's too much code to use later.
From this list:

Table:
Employees
ID | DATA 1 | DATA 2 | DATA 3 | DATA 4 |
----------------------------------------------------------
1 | 0 | 1 | 3 | 2 |
2 | 1 | 0 | 3 | 0 |

Result
ID |    SOURCE    | DATA |
--------------------------------------
1 |    DATA 2 |    1 |
1 |    DATA 4 |    2 |
1 |    DATA 3 |    3 |
2 |    DATA 1 |    1 |
2 |    DATA 3 |    3 |

please use the search function: https://stackoverflow.com/questions/19858079/how-to-convert-columns-into-rows-in-oracle — Esteban P., Aug 29 '17 at 10:47

score 1 · Accepted Answer · answered Aug 29 '17 at 10:57

Here's a way of doing a manual unpivot:

WITH employees AS (SELECT 1 ID, 0 data1, 1 data2, 3 data3, 2 data4 FROM dual UNION ALL
                   SELECT 2 ID, 1 data1, 0 data2, 3 data3, 0 data4 FROM dual)
SELECT e.ID,
       CASE WHEN d.id = 1 THEN 'DATA 1'
            WHEN d.id = 2 THEN 'DATA 2'
            WHEN d.id = 3 THEN 'DATA 3'
            WHEN d.id = 4 THEN 'DATA 4'
       END SOURCE,
       CASE WHEN d.id = 1 THEN data1
            WHEN d.id = 2 THEN data2
            WHEN d.id = 3 THEN data3
            WHEN d.id = 4 THEN data4
       END DATA
FROM  employees e
      CROSS JOIN (SELECT LEVEL ID
                  FROM   dual
                  CONNECT BY LEVEL <= 4 -- the number of columns to unpivot
                  ) d
WHERE  CASE WHEN d.id = 1 THEN data1
            WHEN d.id = 2 THEN data2
            WHEN d.id = 3 THEN data3
            WHEN d.id = 4 THEN data4
       END > 0
ORDER BY ID,
         DATA;

        ID SOURCE       DATA
---------- ------ ----------
         1 DATA 2          1
         1 DATA 4          2
         1 DATA 3          3
         2 DATA 1          1
         2 DATA 3          3

This uses a dummy "table" of 4 rows (since you're unpivoting 4 columns) to cross join to the main table. Then it's just a matter of outputting data1 for row 1, data2 for row 2, etc.

I've used the case statement again in the where clause, but you could do the query without the predicate in a subquery and then add the filter in the outer query if you like (e.g. select id, source, data from (select e.id, case .... ) where data > 0)

Convert columns to rows in oracle 9i

1 Answers1

Linked