Sql question - data driven table generation?

[kmel]

I'm on Oracle 10g

Is it possible to use the data within a table to create another table? In other words, instead of using select column1 from table_1 into table_2, is it possible to use the row-contents of column1 to make table_2? I basically need to pivot a column into a new table (standard function in 11g).

A different way of asking: I have a table with a first_name column. I want to make a new table but instead of copying first_name from table_1, I want to have columns called JOHN, MARY, FRED, AUDREY.....ANDREW. I don't know what names are contained in the first_name column, and they will change all the time, so hardcoding isn't an option.

If you follow me.

 

[LKBrwnDBA]

One of the 10g solutions is a PL/SQL procedure that:

Using the table-name, pivot columns and aggregate function (SUM/MAX/COUNT/etc) as parameters, composes a "CREATE TABLE ... AS SELECT" sql command using the pivot column values as column names and aggregating the pivot data.

Then "execute immediate" the composed statement.


PS: It is easier/faster if you do not code a "generic" procedure.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb