I need to extract one column from sql table and copy it to another table coupled with multiple values. For example, on the source table I select Col1, and copy over to resulting table with first column auto incremental, second column is the column value copied over from source, and third column will be another numeric value, but we need to create 3 records with values 10,20, and 30 for each copied column value.
Source table:
+--------+---------+------+
| Col1 | Col2 | Col3 |
+--------+---------+------+
| Value1 | Value10 | 123 |
| Value2 | Value20 | 123 |
| Value3 | Value30 | 123 |
+--------+---------+------+
secondary table:
+------+--------+-------+
| ColI | Col1 | ColII |
+------+--------+-------+
| 1 | Value1 | 10 |
| 2 | Value1 | 20 |
| 3 | Value1 | 30 |
| 4 | Value2 | 10 |
| 5 | Value2 | 20 |
| 6 | Value2 | 30 |
+------+--------+-------+
What query (MS SQL) to apply on this scenario? The second table is already created with Auto incremental first column, and the multiple values are always (10,20, and 30).
Query: SQLFIddleExample
SELECT t1.Col1, t2.COLII
FROM Table1 t1
CROSS JOIN (SELECT 10 as COLII
UNION ALL
SELECT 20 as COLII
UNION ALL
SELECT 30 as COLII) t2
Result:
| Col1 | COLII |
|--------|-------|
| Value1 | 10 |
| Value1 | 20 |
| Value1 | 30 |
| Value2 | 10 |
| Value2 | 20 |
| Value2 | 30 |
| Value3 | 30 |
| Value3 | 10 |
| Value3 | 20 |
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments