Inverse row to column

Question

I have a big SQL that produces similar output, like this simplified example:

SELECT 5 AS L0, 2 AS L1, 3 AS L2, 4 AS L3 FROM DUAL 

current output is a line:

 | L0 | L1 | L2 | L3 | | 5 | 2 | 3 | 4 | 

The desired output is the following columns:

 | kind | value | | 0 | 5 | | 1 | 2 | | 2 | 3 | | 3 | 4 | 

I know I can get this by combining select 4 times. I am looking for advice if unification is the best I can do here, and if this way out can be achieved by other means.

I also found many examples to invert a column into a row, but here I am looking for inversion from row to column.

Answer 1

Try Unpivot:

 SELECT substr(kind,2,1) AS kind,"value" FROM ( SELECT 5 AS l0, 2 AS l1, 3 AS l2, 4 AS l3 FROM dual )temp unpivot include NULLS ("value" FOR kind IN (l0, l1 , l2, l3)); 

And vice versa:

  SELECT * FROM ( SELECT Kind, max(Value) Value FROM table GROUP BY Kind ) PIVOT ( max(Value) FOR Kind IN (L0, L1, L2, L3) ); 

Assuming you actually have a table for the query.

Answer 2

 select i - 1 kind, 
 case when i = 1 then L0 when i = 2 then L1 when i = 3 then L2 when i = 4 then L3 end as value from your_table, (select level as i from dual connect by level <= 4) 

This makes the Carteasan Product your desktop and “in-line view.” Four lines are displayed in the row representation, from 1 to 4.