c# - Convert 2 datatable to one table -

i have table health:

year   | value1   | value2 2015          2016    bad        2017    bad        bad 

and table money:

year   | money1   |money2 2014    100        200 2015    300        null 2018    500        500 

i want make tmp table this: tmptable

year   |value1   |value2   |money1   |money2 2014    null      null      100       200 2015              300       null 2016    bad            null      null 2017    bad       bad       null      null 2018    null      null      500       500 

can in sql server?

use 2 left joins , use union combine result sets both left joins.

query

select t1.[year], t1.[value1], t1.[value2], t2.[money1], t2.[money2] [health] t1 left join [money] t2 on t1.[year] = t2.[year] union select t1.[year], t2.[value1], t2.[value2], t1.[money1], t1.[money2] [money] t1 left join [health] t2 on t1.[year] = t2.[year] order 1; 

sql fiddle demo