CODEWITHSUNDEEP
sqlsql-servermultiple-columnsmultiple-value
Daleman
Daleman

Reputation: 874

Multiple value insert into single table

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 ?

Upvotes: 0

Views: 79

Answers (3)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Using Union :

        SELECT distinct ID,Category,max(Freq_Activity)Freq_Activity,
        max(Freq_Project)Freq_Project  from
        (
        SELECT ID,Category,Freq_Project,null 'Freq_Activity'
        FROM Tbl_Project TP
        union
        SELECT ID,Category,null 'Freq_Project',Freq_Activity
        FROM Tbl_Activity TP
        )a
        group by ID,Category

Upvotes: 0

vinay koul
vinay koul

Reputation: 346

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

Upvotes: 1

Linkan
Linkan

Reputation: 579

A full join looks like this

SELECT ISNULL(P.ID,A.ID),ISNULL(P.Category,A.Category),P.Freq_Projeckt,A.Freq_activity
FROM Tbl_Project P
FULL OUTER JOIN Tbl_Activity A
ON A.ID=P.ID AND A.Category=P.Category

Upvotes: 0

Related Questions