Reputation: 8156

Oracle: transpose table

I've got the following query

SELECT 1, 2 FROM DUAL

AND I'd like something like

SELECT TRANSPOSE(SELECT 1, 2 FROM DUAL)

Which outputs the same as

SELECT 1 FROM DUAL
UNION
SELECT 2 FROM DUAL

I'd like it to swap lines with columns.

Upvotes: 5

Answers (3)

Reputation: 175964

Using dbms_xmlgen.getxmltype and XMLTABLE:

SELECT *
FROM XMLTABLE('/ROWSET/ROW/*' passing  dbms_xmlgen.getxmltype('SELECT 1, 2, 3 FROM DUAL') 
              COLUMNS val VARCHAR(100) PATH '.');

Advantage over unpivot - there is no need to specify column list in advance

Upvotes: 1

Reputation: 17429

If you don't have Oracle 11g, the best solution is the one you provided:

SELECT 1 FROM DUAL
UNION
SELECT 2 FROM DUAL

Upvotes: 0

Reputation:

Assuming this is Oracle 11, you can use UNPIVOT:

select no from
(SELECT 1 a, 2 b FROM DUAL) dummy
unpivot (no for col in (a as 'A', b as 'B'))

Upvotes: 5