For each row from select insert multiple rows into one table

Question

I want to do something like this

SELECT ID, X, Y, Z FROM TABLE_1

this returns me something like:

| IDTABLE_1 |  X  |  Y  |  Z  |
| 1         |  a  |  b  |  c  |
| 2         |  d  |  e  |  f  |
| 3         |  g  |  h  |  i  |

And i have another table TABLE_2 that has a column with the column names from the previous table (TABLE_1):

| IDTABLE_2 | COLUMN | 
| 101       | X      | 
| 102       | Y      |
| 103       | Z      |

and now i need to insert all my data from the select into this new table TABLE_3 and it has to be like this:

| IDTABLE_3 | IDTABLE_1 | IDTABLE_2   | VALUE |
| 201       | 1         | 101         | a     |
| 202       | 1         | 102         | b     |
| 203       | 1         | 103         | c     |
| 204       | 2         | 101         | d     |
| 205       | 2         | 102         | e     |
| 206       | 2         | 103         | f     |
| 207       | 3         | 101         | g     |
| 208       | 3         | 102         | h     |
| 209       | 3         | 103         | i     |

Any suggestion on a simple way to do it?

Answer 1

SELECT
   IDENTITY(int, 200, 1) IDTABLE_3,
   T1.IDTABLE_1,
   T2.IDTABLE_2,
   CASE T2.COLUMN WHEN 'X' THEN T1.X
                  WHEN 'Y' THEN T1.Y
                  WHEN 'Z' THEN T1.Z END VALUE
INTO TABLE_3
FROM TABLE_1 T1
  CROSS JOIN TABLE_2 T2

Answer 2

You can use the UNPIVOT table operator to do this, like so:

WITH CTE
AS
(
  SELECT IDTABLE_1, ID, value
  FROM Table_1
  UNPIVOT
  (
    ID
    FOR value   IN(x, y,z)
  ) u
)
SELECT 
  ROW_NUMBER() OVER(ORDER BY t1.ID) + 200 AS IDTABLE_3 ,
  t1.IDTABLE_1,
  t2.[IDTABLE_2],
  t1.ID AS VALUE 
FROM CTE t1
INNER JOIN TABLE_2 t2 ON t1.value = t2."Column";

SQL Fiddle Demo

This will give you:

| IDTABLE_3 | IDTABLE_1 | IDTABLE_2 | VALUE |
---------------------------------------------
|       201 |         1 |       101 |     a |
|       202 |         1 |       102 |     b |
|       203 |         1 |       103 |     c |
|       204 |         2 |       101 |     d |
|       205 |         2 |       102 |     e |
|       206 |         2 |       103 |     f |
|       207 |         3 |       101 |     g |
|       208 |         3 |       102 |     h |
|       209 |         3 |       103 |     i |

Answer 3

Depending on your version of SQL Server you can use the UNPIVOT function on the first table, apply a row_number() to each table and then join on the row number. The UNPIVOT function takes the columns X, Y, and Z and converts them to rows. Once this conversion is done you will give each record a row_number() that is partitioned by the IDTABLE_1.

select row_number() over(order by t1.IDTABLE_1) + 200 AS IDTABLE_3,
  t1.IDTABLE_1, t2.IDTABLE_2, t1.value
from
(
  select IDTABLE_1, value, col,
      row_number() over(partition by IDTABLE_1 order by col) rn
  from table1
  unpivot
  (
    value
    for col in (X, Y, Z)
  ) unpiv
) t1
inner join
(
  select IDTABLE_2, [COLUMN],
    row_number() over(order by IDTABLE_2) rn
  from Table2
) t2
  on t1.rn = t2.rn
order by t1.IDTABLE_1;

See SQL Fiddle with Demo

The result is:

| IDTABLE_3 | IDTABLE_1 | IDTABLE_2 | VALUE |
---------------------------------------------
|       201 |         1 |       101 |     a |
|       202 |         1 |       102 |     b |
|       203 |         1 |       103 |     c |
|       204 |         2 |       101 |     d |
|       205 |         2 |       102 |     e |
|       206 |         2 |       103 |     f |
|       207 |         3 |       101 |     g |
|       208 |         3 |       102 |     h |
|       209 |         3 |       103 |     i |