Converting Columns to Rows

Suppose you want to convert an Oracle table:

(id, sum1, sum2, sum3)

into another table:

(id, ‘1′, sum1)
(id, ‘2′, sum2)
(id, ‘3′, sum3)

That means converting 1 row from the first table into 3 rows in the other table. Of course, this can be done by scanning the source table 3 times, one for each «sum» column, but if the first table is pretty large (~50 million rows), we need another, faster approach.

Solution

Using an Inline View with the UNION ALL operator, all can be done in one single step.

CREATE TABLE t1 (
id     NUMBER PRIMARY KEY,
sum1   NUMBER,
sum2   NUMBER,
sum3   NUMBER
);

INSERT INTO t1 VALUES (1,20,40,50);
INSERT INTO t1 VALUES (2,30,20,25);
INSERT INTO t1 VALUES (3,15,60,55);
COMMIT;

select * from t1;

ID       SUM1       SUM2       SUM3
———- ———- ———- ———-
1         20         40         50
2         30         20         25
3         15         60         55

CREATE TABLE t2 AS
SELECT id, num, DECODE(num,’1′,sum1,’2′,sum2,’3′,sum3) data
from t1, (SELECT ‘1′ num FROM dual UNION ALL
SELECT ‘2′ num FROM dual UNION ALL
SELECT ‘3′ num FROM dual)
/

select * from t2 order by id;

ID N       DATA
———- – ———-
1 1         20
1 2         40
1 3         50
2 1         30
2 3         25
2 2         20
3 1         15
3 3         55
3 2         60

(Source : Internet)