Multiple value insert into single table

Question

I need help for query two table become a table

The first table, Tbl_Project:

|ID     | Category  | Freq_Project|
|-------|---------- |-------------|
|12001  |   x_GM    | 1           |
|12001  |   x_PP    | 1           |
|12001  |   x_RI    | 1           |
|12001  |   x_SS    | 3           |

The second table, Tbl_Activity:

|ID     | Category  | Freq_Activity|
|-------|---------- |--------------|
|12001  |   x_GM    |4             |
|12001  |   x_PP    |3             |
|12001  |   x_SA    |2             |
|12001  |   x_RI    |2             |

And, I want the result looks like this

|ID     | Category  | Freq_Activity | Freq_Project|
|-------|---------- |---------------|-------------|
|12001  |   x_GM    |   4           |   1         |
|12001  |   x_PP    |   3           |   1         |
|12001  |   x_SA    |   2           |   **Null**  |
|12001  |   x_RI    |   2           |   1         |
|12001  |   x_SS    |   **Null**    |   3         |

How should I make this query ?

Answer 1

You can use the below code to insert the result in target table.

Insert INTO ResultTable(ID,Category,Freq_Activity,Freq_Project,Tbl_Project)
Select ISNULL(P.ID,A.ID),ISNULL(P.Category,A.Category),A.Freq_Activity,P.Freq_Project,Tbl_Project
from Tbl_Project P
FULL OUTER JOIN Tbl_Activity A
ON P.ID=A.ID
AND P.Category = A.Category