求一个Oracle的语句
有一个STU_LOCATION的表记录学生的所在学校信息，格式如下：
姓名 年级 学校 变化时间
张三 1 学校1 2008-9-1
张三 2 学校1 2009-9-1
张三 3 学校1 2010-9-1
张三 3 学校2 2010-10-1
张三 3 学校3 2011-5-1
（注：没有为0）
想要得到下面这种结果：
姓名 一年级到校时间 学校 二年级到校时间 学校 三年级到校时间 学校
张三 2008-9-1 学校1 2009-9-1 学校1 2010-9-1 学校1
张三 2008-9-1 学校1 2009-9-1 学校1 2010-10-1 学校2
张三 2008-9-1 学校1 2009-9-1 学校1 2011-5-1 学校3
大侠们帮帮忙，小弟新手！
[解决办法]
with test as (
SELECT 'zs' as xm,'1' AS nj,'xx1' as xx,'2008-9-1' as bhsj from dual
union all
SELECT 'zs' as xm,'2' AS nj,'xx1' as xx,'2009-9-1' as bhsj from dual
union all
SELECT 'zs' as xm,'3' AS nj,'xx1' as xx,'2010-9-1' as bhsj from dual
union all
SELECT 'zs' as xm,'3' AS nj,'xx2' as xx,'2010-10-1' as bhsj from dual
union all
SELECT 'zs' as xm,'3' AS nj,'xx3' as xx,'2011-5-1' as bhsj from dual
)
SELECT XM,
XX,
NVL(ONEY, LAG(ONEY, RN - 1, '') OVER(ORDER BY NULL)) AS ONEY,
NVL(ONEBH, LAG(ONEBH, RN - 1, '') OVER(ORDER BY NULL)) AS ONEBH,
NVL(TWOY, LAG(TWOY, RN - 1, '') OVER(ORDER BY NULL)) AS TWOY,
NVL(TWOBH, LAG(TWOBH, RN - 1, '') OVER(ORDER BY NULL)) AS TWOBH,
NVL(THREEY, LAG(THREEY, RN - 1, '') OVER(ORDER BY NULL)) AS THREEY,
NVL(THREEBH, LAG(THREEBH, RN - 1, '') OVER(ORDER BY NULL)) AS THREEBH
FROM (SELECT ROW_NUMBER() OVER(ORDER BY NULL) AS RN, T.*
FROM (select xm,
XX,
max(decode(nj, 1, xx, '')) as ONEY,
max(decode(nj, 1, bhsj, '')) as ONEBH,
max(decode(nj, 2, xx, '')) as TWOY,
max(decode(nj, 2, bhsj, '')) as TWOBH,
max(decode(nj, 3, xx, '')) as THREEY,
max(decode(nj, 3, bhsj, '')) as THREEBH
FROM TEST
GROUP BY XM, XX) T)
