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
Re: Move columns from different tables to one table
Joins are used to join different tables
for about joins follow this [url="http://www.techonthenet.com/sql/joins.php"]link[/url]
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;
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.
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........
Re: Move columns from different tables to one table
[QUOTE=ushalakshmi;29284]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........[/QUOTE]
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;
Re: Move columns from different tables to one table
[QUOTE=ushalakshmi;29284]Select dno,sal into depsal from dept,emp where sal>1000
select * fom depsal
[/QUOTE]
that will not create a table for you .
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;