Want to create a dynamic table using rows from another table

Posted on 2011-04-29
Last Modified: 2013-12-18

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

Please Help me out.
Question by:ajju_1969
    LVL 73

    Expert Comment

    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

    Author Comment


    Pivot will also do.


    Author Comment


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

    LVL 73

    Expert Comment

    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.  
    LVL 76

    Accepted Solution

    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'));
               EXTRACT(XMLAGG(XMLELEMENT("s", col1 || ',')), '/s/text()').getclobval(),
    FROM tab1

    Open in new window

    LVL 73

    Expert Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now