有一个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,