Compare two tables and get output with unmatched data with respective column name

Question

I have two tables similar to following construct. I need to compare both and get the output as shown in results.

Here I am comparing column1 with column4, column2 with column5 and column3 and column6.

Table1:

Key column1 column2 column3
1    4       5       6
2    2       5       8
3    4       5       10
4    4       6       10

Table2:

Key column4 column5 column6
1    6       5       6
2    2       5       8
3    4       5       10
4    4       8       10

Result should be:

Key1 Table2ColumnName Table1ColumnValue Table2ColumnValue
1    column4              4                  6
4    column5              6                  8

Note : In actual case, I have tables with huge number of columns to compare. Column names in both table are different.

Could someone kindly guide on how to achieve the above requirement.

How do you map from `ColumnA` to `ColumnX` ? or are you just comparing Ordinal values? — Christian Phillips, Jul 29 '15 at 15:38
Why do you only want those rows and columns in your result? What is the logic behind selecting which Key and Table2ColumnName to return? — Tab Alleman, Jul 29 '15 at 15:45

score 1 · Answer 1 · answered Jul 29 '15 at 15:54

I don't think this will be solved with a SQL query since you want to do this dynamically and there isn't a consistent naming for the columns. The example you gave is short and sweet but you mentioned you have 'tables with huge number of columns to compare'.

You might be better off importing each table you want to compare into an Excel spreadsheet (one table per sheet) and doing some VLOOKUP formulas.

Compare two tables and get output with unmatched data with respective column name

1 Answers1