Archives du mot-clé rotate

Rotating table with Oracle UNPIVOT

I had recently faced a SQL question about rotating a table

The question was how to rotate transform a table gettint multiple columns into rows

Here is a small sample to create the table

CREATE TABLE test_tab(productm VARCHAR2(30),
                      leg1 VARCHAR2(30),
                      legval1 INTEGER,
                      leg2 VARCHAR2(30),
                      legval2 INTEGER,
                      leg3 VARCHAR2(30),
                      legval3 INTEGER);

INSERT INTO test_tab
VALUES ('prod 1', 'subprod 1-1', 11, 'subprod 1-2', 12, 'subprod 1-3', 13);
INSERT INTO test_tab
VALUES ('prod 2', 'subprod 2-1', 21, 'subprod 2-2', 22, 'subprod 2-3', 23);
INSERT INTO test_tab
VALUES ('prod 3', 'subprod 3-1', 31, 'subprod 3-2', 32, 'subprod 3-3', 33);
INSERT INTO test_tab
VALUES ('prod 4', 'subprod 4-1', 41, 'subprod 4-2', 42, NULL, NULL);
INSERT INTO test_tab
VALUES ('prod 5', 'subprod 5-1', 51, NULL, NULL, NULL, NULL);
COMMIT;

SELECT * FROM test_tab;

PRODUCTM  LEG1         LEGVAL1  LEG2         LEGVAL2  LEG3         LEGVAL3
--------- ------------ -------- ------------ -------- ------------ --------
prod 1    subprod 1-1        11 subprod 1-2        12 subprod 1-3        13
prod 2    subprod 2-1        21 subprod 2-2        22 subprod 2-3        23
prod 3    subprod 3-1        31 subprod 3-2        32 subprod 3-3        33
prod 4    subprod 4-1        41 subprod 4-2        42                 
prod 5    subprod 5-1        51                                          

And the result needed was something like that

PRODUCTM         PRODNAME         PRODVAL
---------------- ---------------- ---------------
prod 1           subprod 1-1                   11
prod 1           subprod 1-2                   12
prod 1           subprod 1-3                   13
prod 2           subprod 2-1                   21
prod 2           subprod 2-2                   22
prod 2           subprod 2-3                   23
prod 3           subprod 3-1                   31
prod 3           subprod 3-2                   32
prod 3           subprod 3-3                   33
prod 4           subprod 4-1                   41
prod 4           subprod 4-2                   42
prod 5           subprod 5-1                   51

After some researchs i finally found that Oracle 11G got the solution for me

SELECT productm, prodname, prodval
FROM   test_tab unpivot((prodname, prodval) 
FOR    prod IN( (leg1, legval1),
                (leg2, legval2),
                (leg3, legval3)));