1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

create table with many columns into one column

Discussion in 'SQL PL/SQL' started by sai, Jul 16, 2009.

  1. sai

    sai Guest

    Hi

    I have to create a table with 2 columns

    first column gets the values from sno of other table
    second column gets the values from morethan one column of other table

    how can i select more than one column into a single column while creating a new table ?

    thanks...sai
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    you need to use CTAS or create table as select from...

    I can't really say more without you giving me some test cases.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here's one way:
    Code (Text):

    SQL> create table empinfo
      2  as select e.empno, d.deptno||','||d.dname||','||d.loc depinfo
      3  from emp e, dept d
      4  where d.deptno = e.deptno
      5  /

    Table created.

    SQL>
    SQL> select * from empinfo;

         EMPNO DEPINFO
    ---------- ---------------------------------------------------------------------
          7369 20,RESEARCH,DALLAS
          7499 30,SALES,CHICAGO
          7521 30,SALES,CHICAGO
          7566 20,RESEARCH,DALLAS
          7654 30,SALES,CHICAGO
          7698 30,SALES,CHICAGO
          7782 10,ACCOUNTING,NEW YORK
          7788 20,RESEARCH,DALLAS
          7839 10,ACCOUNTING,NEW YORK
          7844 30,SALES,CHICAGO
          7876 20,RESEARCH,DALLAS

         EMPNO DEPINFO
    ---------- ---------------------------------------------------------------------
          7900 30,SALES,CHICAGO
          7902 20,RESEARCH,DALLAS
          7934 10,ACCOUNTING,NEW YORK

    14 rows selected.

    SQL>