• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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.
0
ajju_1969
Asked:
ajju_1969
  • 3
  • 2
1 Solution
 
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
 
ajju_1969Author Commented:
Hi,

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

Thanks
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
slightwv (䄆 Netminder) 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:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now