Converting rows to clumns (max case)

[mzladyd]

This forum has been so helpful to me. Mr Moderator, I know I should at least attempt this myself before asking for help, but I don't know where to begin. My max case 'sql' works fine, but I my 'when' statement needs to be dynamic. I am extracting a set of data from Teradata (current year and 2 previous years) and loading it into an Oracle table. The data is in 3 columns so I max case to pivot the table to:

EMPNO YEAR1 RATING1 YEAR2 RATING2 YEAR3 RATING3

I want to run this sql after the load to Oracle to create a new table in the format above. My problem is that that years will change. My year1 should always be equal to the current year, year2 = current year -1 and year3 = current year - 2. How do I set these parameters within my case statement?

I'm thinking , max (case YEAR when YEAR = CUR_YEAR then YEAR end) as YEAR1, etc.

Thanks in advance. I appreciate any and all help.


select EMPNO
, max (case YEAR when 2007 then YEAR end) as YEAR1
, max (case YEAR when 2007 then RATING end) as RATING1
, max (case YEAR when 2006 then YEAR end) as YEAR2
, max (case YEAR when 2006 then RATING end) as RATING2
, max (case YEAR when 2005 then YEAR end) as YEAR3
, max (case YEAR when 2005 then RATING end) as RATING3
from MYTABLE
group by EMPNO;