Transpose a single row to multiple Oracle rows

Question

I have a query that always returns a single row with many columns. I would like to turn this into 2 columns and many rows.

Original results:

Col1, Col2, Col3, Col4
----------------------
val1, val2, val3, val4

What I want:

ColName, Value
--------------
Col1,    val1
Col2,    val2
Col3,    val3
Col4,    val4

Is it possible?

EDIT (clarification)

I am looking for an automatic way to do this. IE, then I can pass the results from any query that returns only 1 row.

Answer 1

Are you using Oracle 11g? Did you try to collapse and not open?

More details here .

Answer 2

sure. At

select 'Col1' ColName, Col1 Value from srctable union all
select 'Col2', Col2 from srctable union all
select 'Col3', Col3 from srctable union all
select 'Col4', Col4 from srctable

Answer 3

I assume that you do not have an Oracle version with native Pivot table support. Unfortunately, this is never very clean code, since you need to create each line.

See this older post for an example.

transpose-select-results-with-oracle