Results 1 to 7 of 7

Thread: Move columns from different tables to one table

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Mar 2008
    Answers
    1

    Move columns from different tables to one table

    How to select columns from different tables and move all those columns in one table for example we

    select sal from dept where sal>1000
    (in this we get one column regarding sal greater than 1000)
    select deptno from dept;

    overall we get two columns .my qns is how to put these two different columns in one table


  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Move columns from different tables to one table

    Joins are used to join different tables
    for about joins follow this link
    create table deptwisesal(deptno number(2),sal number(5));


    insert into deptwisesal select d.deptno,e.sal from emp e,dept d where e.deptno=d.deptno and e.sal>1000


    drop table deptwisesal;


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Move columns from different tables to one table

    You can't move a column of existing table to another table. Just follow as suggested in the previous post for best alternative.


  4. #4
    Junior Member
    Join Date
    Apr 2008
    Answers
    15

    Re: Move columns from different tables to one table

    u can do this by using select into query.........

    For Ex,

    Select dno,sal into depsal from dept,emp where sal>1000
    select * fom depsal


    then we get the new table depsal with dno,sal whose sal >1000........


  5. #5
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Move columns from different tables to one table

    Quote Originally Posted by ushalakshmi View Post
    u can do this by using select into query.........

    For Ex,

    Select dno,sal into depsal from dept,emp where sal>1000
    select * fom depsal


    then we get the new table depsal with dno,sal whose sal >1000........
    Select into is used in PL/SQL to dump value of a column to a variable. It will not create a new table.

    In a single statemt you can do it in the following way
    CREATE TABLE deptwisesal AS
    SELECT emp.sal, dept.deptno
    FROM emp,dept
    where emp.deptno = dept.deptno;


  6. #6
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Move columns from different tables to one table

    Quote Originally Posted by ushalakshmi View Post
    Select dno,sal into depsal from dept,emp where sal>1000
    select * fom depsal
    that will not create a table for you .


  7. #7
    Junior Member
    Join Date
    Sep 2007
    Answers
    2

    Re: Move columns from different tables to one table

    There are 2 condition for create table which consist of columns from 2 table

    1. Table having common column to join 2 table
    2. Table not having common column to join 2 table

    If there is a linking column (common column on the basis of which 2 tables can be connected) between 2 table then u can create table like this

    create table dept_emp as
    select dept.dname, emp.ename
    from emp, dept
    where emp.deptno = dept.deptno;


    If 2 table from which column is to be taken are not having same column
    then u need to use joins

    --say for example i have 2 table
    create table t1 (num1 number, num2 number);
    insert into t1 values(1,1);
    insert into t1 values(2,2);
    insert into t1 values(3,3);
    insert into t1 values(4,4);

    create table t2 (str1 varchar2(20), str2 varchar2(20));
    insert into t2 values('a','a');
    insert into t2 values('b','b');
    insert into t2 values('c','c');
    insert into t2 values('d','d');

    -- To create table with data
    create table t1_t2 as
    select num1,NULL str1 from t1
    UNION
    select NULL,str1 from t2;

    select * from t1_t2;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact