SQL Query make columns results into rows

Question

I'm using reporting services to make a report graph. However my data looks like this:

Table1
    C01    C02   C03   C04
    1      2     3     4

I need to do a sql query to return data that looks like this:
    Any_Col_name
    1
    2
    3
    4

I'm using MS Reporting Services with a Oracle DB. I cannot restructure the table.

Answer 1

Have a look here.

http://support.microsoft.com/kb/175574

It describes how to "rotate" a table in SQL Server. I know you said Oracle buy you might glean something from it.

Answer 2

select c01 from table
union all
select c02 from table
union all
select c03 from table
union all
select c04 from table

Answer 3

If you are using Oracle 11G and above, you can also use unpivot for this, it should be more efficient than the union all (haven't tested this cause I do not have oracle around)

SELECT Any_Col_name  FROM table   
    UNPIVOT INCLUDE NULLS (Any_Col_name FOR Col IN (C01,C02,C03,C04))

Answer 4

We have solved this problem many times. Your best plan of action is to write appropriate PL/SQL functions that will iterate through all of the columns, outputting them as rows. I say to do it this way, because this will probably not be the last time you use this functionality.