SQL & PL/SQL

Hi,

The following fields are key values on a table, with example data :

EMPNO DATE ACTION
1234 6/6/05 HIR
1234 6/6/05 XFR
1234 6/10/05 DTA
1234 6/22/05 XFR
1234 7/30/05 TER
3456 6/6/05 HIR

etc.

I need to create a view to show the previous 5 rows of history per employee but flattened to be one line per Employee Number (EMPNO).

So the view would be something like :

EMPNO ACTION1 DATE1 ACTION2 DATE2 ACTION3 DATE3 (etc.)
1234 HIR 6/6/05 XFR 6/6/05 DTA 6/10/05

I have thought of aliasing the table and then using correlated subqueries, but this got complicated joining to >2 tables. Since joining to table 2 required that DATE1 <> DATE2 or ACTION1 <> ACTION2. But then alias 3 becomes more convoluted and then 4 and 5 OTT.

I also looked at some rownum trickery to get the <=5 rows, but I could not get this working.

What is the best solution for this problem ? Note that it does have to be a pure view, not PL/SQL etc.

Thanks,

T0rrent