CODEWITHSUNDEEP
sqlsql-server
Rob M
Rob M

Reputation: 55

SQL table merging

Using SQL Server 2008, I'll try my best to describe what I want to do.

Let's say I have table A that looks like this:

Table A

VarName  |  ValueA  |  ValueB
-----------------------------
var1     |   0.12   |   0.34
var2     |   1.456  |   5.31
var3     |   43     |    10

And a table B that looks like this:

Table B

VarName  |  ValueC  |  ValueD
-----------------------------
var1     |   0.03   |   0.04
var2     |   2.01   |   2.87
newvar   |   55     |    50

What I want to do is combine these tables so that the end result looks like this table C.

Table C

VarName  |  ValueA  |  ValueB  |  ValueC  |  ValueD
---------------------------------------------------
var1     |   0.12   |   0.34   |   0.03   |  0.04
var2     |   1.456  |   5.31   |   2.01   |  2.87
var3     |   43     |    10    |    0     |   0
newvar   |   0      |    0     |    55    |   50

Note that when merged, the common rows (var names) are updated with values from the other table in the columns that are not common, and variables not present in one table have their columns merged from the other table set to 0 such as newvar from table B and var3 from table A.

Does this request make sense or does it require further explanation?

Upvotes: 2

Views: 58

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

How about this: 

SELECT 
  VarName
  ,MAX(IValueA) AS ValueA
  ,MAX(ValueB) AS ValueB
  ,MAX(ValueC) AS ValueC
  ,MAX(ValueD) AS ValueD
FROM (  
SELECT 
  VarName
  ,ValueA
  ,ValueB
  ,0 AS ValueC
  ,0 AS ValueD
FROM A
UNION ALL
SELECT 
  VarName
  ,0 AS ValueA
  ,0 AS ValueB
  ,ValueC
  ,ValueD  
FROM B
) a
GROUP BY VarName

Upvotes: 0

Iłya Bursov
Iłya Bursov

Reputation: 24146

you can try:

select VarName, max(ValueA), max(ValueB), max(ValueC), max(ValueD) from (
    select VarName, ValueA, ValueB, 0 as ValueC, 0 as ValueD
    from TableA
    union all
    select VarName, 0, 0, ValueC, ValueD
    from TableB
)
group by VarName

Upvotes: 1

Related Questions