CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
bsc
bsc

Reputation: 3

Combining tables SQL Server 2005

I need a Result Table as:

How do you generate this 3rd table? I have tried all kinds of joins - full, left, right, etc.

Upvotes: 0

Views: 222

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

EDIT

select
    A.LocID,
    A.Prod_ID,
    B.Metric_ID,
    coalesce(C.Metric_Data + D.Metric_Data, C.Metric_Data, D.Metric_Data) Metric_Data
from (
    select LocID, Prod_ID from table1 group by LocID, Prod_ID) A
inner join (
    select LocID, Metric_ID from table1 group by LocID
    union
    select LocID, Metric_ID from table2 group by LocID) B on A.LocID = B.LocID
left join table1 C on C.LocID = A.LocID and C.Prod_ID = A.Prod_ID and C.Metric_ID = B.Metric_ID
left join table2 D on D.LocID = A.LocID and D.Metric_ID = B.Metric_ID

Notes:

  • A: produces all the location and ProdID combinations
  • B: produces, for each location, all the possible MetricIDs from both tables
  • C and D: left joins to the data tables to get the Metric Data
  • Coalesce: returns either C + D, or if one of them is null, return the other
select
    coalesce(a.LocID, b.LocID) LocID,
    a.Prod_ID,
    coalesce(a.Metric_ID, b.Metric_ID) Metric_ID,
    coalesce(a.Metric_Data + b.Metric_Data, a.Metric_Data, b.Metric_Data) Metric_Data
from table1 a
full outer join table2 b
    on a.LocID = b.LocID and a.Metric_ID = b.Metric_ID

This assumes

  1. You are matching by the tuple (LocID, Metric_ID)
  2. It is possible for either A or B not to have (LocID,Metric_ID) that exists in the other
  3. The result of Metric_Data is either A+B (if both exist), or A or B if only one exists for a (LocID, Metric_ID) combination

Upvotes: 1

Related Questions