create new table with input of two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create new table with input of two tables

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-06-20 : 08:55:47

table1:

orderid product1 product2 product3 product4
100 1 1 1 1
102 1 2 2 4
103 4 1 4 3

product

product product_id
product1 1
product2 2
product3 3

I have to create a new table which should
join table1 and product and the output should
look like this:

orderid product_id quantity
100 1 1
100 2 1
100 3 1
100 4 1
102 1 1
102 2 2
102 3 2
102 4 4
103 1 4
103 2 1
103 3 4
103 4 3

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-20 : 09:03:53

select a.orderid, b.product_id, a.q
from
(
select orderid, type = 'product1', q = product1 from table1
union all
select orderid, type = 'product2', q = product2 from table1
union all
select orderid, type = 'product3', q = product3 from table1
union all
select orderid, type = 'product4', q = product4 from table1
) a
join product b
on a.type = b.product

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 20:07:23

or use unpivot


SELECT m.orderid,n.product_id,m.quantity 
FROM
(
SELECT orderid, product,quantity 
FROM table1
UNPIVOT (quantity FOR product IN (product1,product2,product3,product4))u
)m
INNER JOIN product n
on n.product = m.product

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources