Want to create a dynamic table using rows from another table

Hi,

I want to create a Table with number of columns equal to number of rows selected from another table

Please Help me out.
ajju_1969Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If you are looking for more of an unlimited and can use delimited text like a CSV, try concatination.
drop table tab1 purge;
create table tab1(col1 char(1));

--generate 5000 rows
insert into tab1 (select 'a' from xmltable('1 to 5000'));
commit;

SELECT 
       RTRIM(
           EXTRACT(XMLAGG(XMLELEMENT("s", col1 || ',')), '/s/text()').getclobval(),
           ','
       )
           myCSV
FROM tab1
/

Open in new window

0
 
sdstuberCommented:
Do you actually need a "table" or just a query that pivots the results?

Number of rows could be unlimited,  number of columns can not, you can only have 1000 columns in a table
0
 
ajju_1969Author Commented:
Hi,

Pivot will also do.

Thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ajju_1969Author Commented:
Hi,

Update on this, We are using Oracle 10g Database.

Thanks
0
 
sdstuberCommented:
please provide sample data and expected results

also note,  you'll likely get more attention to your questions if you don't redue the points to the minimum amount  Points are supposed to represent importance to you as the asker.  So low point questions are not important to you,  higher point questions are more important.  
0
 
sdstuberCommented:
11g has a PIVOT function
but for 10g and lower

you'll have to do conditional aggregates

that is, you must create a condition that identifies which row gets pivoted to which column.  In my example below I'm using rownum sorted by my table names  values.  Then use an aggregate like MAX or MIN to pull them all into a single row

SELECT MAX(DECODE(rn, 1, x)),
       MAX(DECODE(rn, 2, x)),
       MAX(DECODE(rn, 3, x)),
       MAX(DECODE(rn, 4, x)),
       MAX(DECODE(rn, 5, x)),
       MAX(DECODE(rn, 6, x)),
       MAX(DECODE(rn, 7, x)),
       MAX(DECODE(rn, 8, x))
  FROM (SELECT ROWNUM rn, user_tables.table_name x FROM user_tables)

if you want to create a table,  just put

CREATE TABLE new_table AS

in front of the select




0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.