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.

Answer

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 '.');

db<>fiddle demo

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