How would you create the data in table1 given the data in table2?

Question

I know SQL pretty well for joins and queries, but when it comes to this I have no idea. Can someone take a crack at this, please?

How would you create the data in table1 given the data in table2? Write an SQL query.

Table1 (csv):

order_id1, prod_id1
order_id1, prod_id2
order_id1, prod_id3
order_id1, prod_id4
order_id1, prod_id4

Table2 (csv):

order_id1, prod_id1, prod_id2, prod_id3, prod_id4, prod_id4

Answer 1

The syntax for this kind of INSERT will vary depending upon the database platform you are using. The below works for SQL Server:

insert into table1
(order_id, prod_id)
select order_id1, prod_id1 from table2
union all
select order_id1, prod_id2 from table2
union all
select order_id1, prod_id3 from table2
union all
select order_id1, prod_id4 from table2

Note: You have two prod_id4 columns in your example, so I left one out, assuming it is a mistake

Answer 2

no way, you got to do a script coz you dont even know how many lines there are int the table1 if the number of lines is fixed, than you can do as RedFiter said otherwise you may write procedure with a loop inside