Getting a specific row value in a column

Question

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

Answer 1

Assuming your real table has some more rows (i.e. more years and more periods) than you show us, this would be a more generic solution:

WITH t AS   
    (SELECT YEAR,Period,prd_Desc,Amount, 
       LEAD(amount, 1) OVER (PARTITION BY prd_Desc, period ORDER BY YEAR) AS amount_next_year 
    FROM a_table)
SELECT * 
FROM t
WHERE amount_next_year IS NOT NULL;

In case values in column YEAR are not continuous, you can do it like this:

WITH t AS   
    (SELECT YEAR,Period,prd_Desc,Amount, 
        LAST_VALUE(amount) OVER 
        (PARTITION BY period, prd_Desc ORDER BY YEAR 
            RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS amount_next_year 
    FROM A_TABLE)
SELECT * 
FROM t
WHERE amount_next_year IS NOT NULL;