CODEWITHSUNDEEP
sqlsql-servert-sql
Javad Abedi
Javad Abedi

Reputation: 512

how can SELECT a row of TABLE and then INSERT to another

IN SQL SERVER 2014.

Query of SQL

I want create table FCD_Assembly with two tables of FCD and Assembly .

my table like this :

======================|| Assembly TABLE ||=========================

+-------------+-------+-------+-------+-------+-------+
| ID_Assembly | Col_1 | Col_2 | Col_3 | Col_4 | Col_5 |
+-------------+-------+-------+-------+-------+-------+
| 1200        | 0     | 0     | 1     | 0     | 0     |
| 1201        | 1     | 0     | 0     | 0     | 1     |
| 1202        | 0     | 1     | 0     | 0     | 0     |
| 1203        | 0     | 0     | 0     | 1     | 0     |
| 1204        | 0     | 1     | 0     | 0     | 1     |

======================|| FCD TABLE ||=========================

+--------+--------+------+
| ID_FCD | Tittle | type |
+--------+--------+------+
| 1      | Col_1  | F    |
| 2      | Col_2  | F    |
| 3      | Col_3  | F    |
| 4      | Col_4  | C    |
| 5      | Col_5  | D    |

======================|| FCD_Assembly TABLE ||=========================

+-------------+-------------+
| ID_FCD      | ID_Assembly |
+-------------+-------------+
| 3           | 1200        |
| 1           | 1201        |
| 5           | 1201        |
| 2           | 1202        |
| 4           | 1203        |
| 2           | 1204        |
| 5           | 1204        |

I want create table FCD_Assembly

thank you for your help :)

Upvotes: 1

Views: 98

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You need to use unpivot for Assembly table let the column name to row value, then do join with FCD table.

I would use CROSS APPLY to do unpivot.

SELECT f.ID_Assembly,t1.id 
FROM (
    select v.* from Assembly CROSS APPLY 
    (
        VALUES 
        (ID_Assembly,Col_1,'Col_1'),
        (ID_Assembly,Col_2,'Col_2'),
        (ID_Assembly,Col_3,'Col_3'),
        (ID_Assembly,Col_4,'Col_4'),
        (ID_Assembly,Col_5,'Col_5')
    )  v (id,val,Title)
    WHERE v.val <> 0
)t1 JOIN FCD f on t1.Title = f.Tittle

sqlifddle

Result

ID_FCD  ID_Assembly
3       1200
1       1201
5       1201
2       1202
4       1203
2       1204
5       1204

Upvotes: 1

Related Questions