Get Records from SYS.DUAL in rowwise

Wenky

HI ,

 I would like to know whether we can get the rows from dual in rows.

For Example:

select 'a','b','c' from Dual.

Ouput:

'A' 'B' 'C'

a   b   c

But I required output in rowwise.i.e.,

Required Output:

a

b

c

I need a solution with nested query.without union and level commands Any help in this regard is highly appreciated.

Wenky

This is one solution using union.

Just for reference

select 'a' from dual union select 'b' from dual union select 'c' from dual;

Nathan Johnson

I think you want UNPIVOT: select value from ( (select 'a' a, 'b' b, 'c' c from dual) unpivot (value for value_type in (a, b, c)) ) Further info in the PIVOT article :
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

Wenky

Thanks for the solution Johnson. The solution is good but it is not working in my case.I'm getting identifier too long exception in oracle. i'm copying sample query of mine.


select value from ( (select 'Ie170f580807f11dba40cc0b31b37cea1' Ie170f580807f11dba40cc0b31b37cea1, 'Ie17c1910807f11dba40cc0b31b37cea1' Ie17c1910807f11dba40cc0b31b37cea1, 'Ie17c8e40807f11dba40cc0b31b37cea1' Ie17c8e40807f11dba40cc0b31b37cea1 from dual) unpivot (value for value_type in (Ie170f580807f11dba40cc0b31b37cea1, Ie17c1910807f11dba40cc0b31b37cea1, Ie17c8e40807f11dba40cc0b31b37cea1)));

Nathan Johnson

That's because your identifiers are too long. Oracle has a limit of 30 characters for identifiers. Try this: select value from ( (select 'Ie170f580807f11dba40cc0b31b37cea1' a, 'Ie17c1910807f11dba40cc0b31b37cea1' b, 'Ie17c8e40807f11dba40cc0b31b37cea1' c from dual) unpivot (value for value_type in (a, b, c)) );