CODEWITHSUNDEEP
sqlsql-serversql-server-2008
unknown6708
unknown6708

Reputation: 55

SQL Join two tables with same column names, and get sum of each column

I know that in the long run... the column names being the same is probably irrelevant. But here's what I've got:

Master Table:

| PersonID | 1970 | 1971 | 1972 | 1973 |
|----------|------|------|------|------|
|        1 |   50 |   50 |  100 |   50 |
|        2 |   30 |   30 |   40 |   40 |
|        3 |   40 |   40 |   40 |   20 |

Supplemental Table

| PersonID |   1972 |   1973 |
|----------|--------|--------|
|        1 |    100 | (null) |
|        2 | (null) | (null) |
|        3 | (null) |    200 |

I'd like to have a table (a view, actually) that will join these two tables, and add the sum of the columns into a column with the same name

Combined Table:

| PersonID | 1970 | 1971 | 1972 | 1973 |
|----------|------|------|------|------|
|        1 |   50 |   50 |  200 |   50 |
|        2 |   30 |   30 |   40 |   40 |
|        3 |   40 |   40 |   40 |  220 |

The resulting table needs to have both tables combined.. The years go all the way to 2017, and are the same name for each table.

SQLfiddle

Upvotes: 2

Views: 1480

Answers (2)

Mike Hill
Mike Hill

Reputation: 3772

Unless you choose to use dynamic SQL (generally best to avoid to prevent SQL injection, bugs, and negative performance impacts), the column names being equivalent doesn't give us any shortcuts here.

The canonical way to do this would be to sum each column explicitly:

SELECT mt.PersonID, COALESCE(mt.[1970], 0) + COALESCE(st.[1970], 0), COALESCE(mt.[1971], 0) + COALESCE(st.[1971], 0), ... COALESCE(mt.[2017], 0) + COALESCE(st.[2017], 0)
FROM MasterTable mt
LEFT JOIN SupplementalTable st
    ON mt.PersonID = st.PersonID;

However, this sucks with 40 columns (and it sounds like possibly more in the future). Dynamic SQL is generally best to avoid, but sometimes it's still the best tool for the job. Here's how you could solve the same problem with dynamic SQL:

DECLARE @dynamicSql NVARCHAR(MAX);
DECLARE @selectList NVARCHAR(MAX);

SELECT @selectList = COALESCE(@selectList + ', ', '') + 'COALESCE(mt.[' + mtColumns.COLUMN_NAME + '], 0)' + COALESCE(' + COALESCE(st.[' + stColumns.COLUMN_NAME + '], 0)', '')
FROM INFORMATION_SCHEMA.COLUMNS mtColumns
LEFT JOIN INFORMATION_SCHEMA.COLUMNS stColumns ON mtColumns.COLUMN_NAME = stColumns.COLUMN_NAME
WHERE mtColumns.TABLE_NAME = 'MasterTable'
      AND stColumns.TABLE_NAME = 'SupplementaryTable';

SET @dynamicSql = 'SELECT ' + @selectList + ' FROM MasterTable mt INNER JOIN SupplementaryTable st ON mt.PersonID = st.PersonID;';

EXECUTE sp_executesql @dynamicSql;

I haven't generated the actual tables to test this, but it should be fairly close. The query should generate a list of SELECT field sums for each field and then run it. It should also skip non-existent fields in SupplementaryTable, and it will only operate for fields that do exist in MasterTable.

Using an ORM (like Hibernate) can do a great deal towards mitigating potential bugs or SQL injection vulnerabilities by validating column names ahead of time.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If there is only one matching row per id, then left join does it:

select t1.person_id, t1.[1970], t1.[1971],
       (t1.[1972] + coalesce(t2.[1972], 0)) as [1972],
       (t1.[1973] + coalesce(t2.[1973], 0)) as [1973]       
from t1 left join
     t2
     on t1.person_id = t2.person_id

Upvotes: 2

Related Questions