Create table on the fly

[CassidyHunt]

I need to be able to take a distinct column query and turn the data into the columns of a table on the fly. I.E.

Column_1 Column_2
--------------------------
red 1
blue 2
green 3

to

Red Blue Green
--------------------------
1 2 3


Is there anyone to do this without knowing the numbers of columns that can be produced in advance or know anything about the data contained in the new entity?

Thanks

Cassidy

 

[Ascalonian]

Check out "EXECUTE IMMEDIATE

 

[CassidyHunt]

I don't see how execute immediate would work unless your talking about running a p-line function. In which case without a predictable table size you could not create the function to output the table on the fly.

I was leaning more towards this method however it hasn't been compiling for me:

http://www.oracle.com/technology/oramag/code/tips2004/050304.html

Thanks

Cassidy

 

[SantaMufasa]

Cassidy,

Did you see my posting in the other "pivot" thread to which you posted? "rowtocol" should work if you ensure that you successfully run Krishna's "CREATE FUNCTION ROWTOCOL..." code.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”