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 | | |
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;
You can join these tables and pivot as below:
Select *
from (
Select
a.Id,
a.[Name],
b.[Value],
b.[year]
from tablea a
left join tableb b on a.Id = b.id
) a
pivot (
max([Value])
for [Year] in ([2016],[2017])
) p
Try this:
SELECT
tbla.id as Id,
tbla.name as Name,
(SELECT
value
FROM tblb
WHERE tblb.Id = tbla.id
AND tblb.Year ='2016') as 2016,
(SELECT
value
FROM tblb
WHERE tblb.Id = tbla.id
AND tblb.Year ='2017') as 2017
FROM tbla;
