Select multiple rows to columns in SQL server

Question

I want below output through SQL server.

TABLE A

| Id   |   Name   | 2016 | 2017 |  
 - - - - - - - - -  - - - - - - - 
| 1    |  ABCDEFG |      |      |
| 2    |  XYZLMON |      |      |

TABLE B

| Id   | Value | Year |  
 - - - - - - - - - - -
| 1    |  F    | 2016 |
| 1    |  G    | 2017 |

OUTPUT

| Id   |   Name   | 2016 | 2017 |  
 - - - - - - - - -  - - - - - - - 
| 1    |  ABCDEFG |  F   |  G   |
| 2    |  XYZLMON |      |      |

Answer 1

You can do this with two joins:

select a.*, b2016.value as val2016, b2017.value as val2017
from a left join
     b b2016
     on a.id = b.id and b.year = 2016 left join
     b b2017
     on a.id = b.id and b.year = 2017;