I have data for current year as well as next year in a table.
Sample Table data:
Year,Period,prd_Desc,Amount
2014,11, Humira, 120
2015,11, humira, 140
Key Coulmn are Year,Period,prd_Desc
If the data present for next year and for same period, i need that value in a separate column. Like below
Year,Period,prd_Desc,Amount_curnt_yr,Amount_next_yr
2014,11, humira, 120, 140
I can achive this by doint a left outer join between same table using below query:
select a.Year,a.Period,a.prd_Desc,a.Amount as Amount_curnt_yr,b.Amount as Amount_next_yr
from (select Year,Period,prd_Desc,Amount
from tableA) a
left outer join (select Year,Period,prd_Desc,Amount from tableA) b on
b.year=a.year+1 and a.Period=b.period and a.prd_Desc=b.prd_Desc
I was trying to get it in a simngle query without using left outer join, but could not. If anybody can share any idea, that would helps
