WITH TB AS (SELECT PID, NUM, SUMGRADE, ROW_NUMBER() OVER(PARTITION BY PID ORDER BY NUM DESC) RN FROM MYTABLE) SELECT pid, max(DECODE(rn,1,NUM,NULL)) num1, max(DECODE(rn,1,SUMGRADE,NULL)) SUMGRADE1, max(DECODE(rn,2,NUM,NULL)) num2, max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE2, max(DECODE(rn,3,NUM,NULL)) num3, max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE3, max(DECODE(rn,4,NUM,NULL)) num4, max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE4 FROM TB GROUP BY pid
WITH TB AS (SELECT PID, NUM, SUMGRADE, ROW_NUMBER() OVER(PARTITION BY PID ORDER BY NUM DESC) RN FROM MYTABLE) SELECT pid, max(DECODE(rn,1,NUM,NULL)) num1, max(DECODE(rn,1,SUMGRADE,NULL)) SUMGRADE1, max(DECODE(rn,2,NUM,NULL)) num2, max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE2, max(DECODE(rn,3,NUM,NULL)) num3, max(DECODE(rn,3,SUMGRADE,NULL)) SUMGRADE3, max(DECODE(rn,4,NUM,NULL)) num4, max(DECODE(rn,4,SUMGRADE,NULL)) SUMGRADE4 FROM TB GROUP BY pid
WITH TB AS
(SELECT PID,
NUM,
SUMGRADE,
ROW_NUMBER() OVER(PARTITION BY PID ORDER BY NUM DESC) RN
FROM MYTABLE)
SELECT
pid,
max(DECODE(rn,1,NUM,NULL)) num1,
max(DECODE(rn,1,SUMGRADE,NULL)) SUMGRADE1,
max(DECODE(rn,2,NUM,NULL)) num2,
max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE2,
max(DECODE(rn,3,NUM,NULL)) num3,
max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE3,
max(DECODE(rn,4,NUM,NULL)) num4,
max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE4
FROM TB
GROUP BY pid
WITH TB AS
(SELECT PID,
NUM,
SUMGRADE,
ROW_NUMBER() OVER(PARTITION BY PID ORDER BY NUM DESC) RN
FROM MYTABLE)
SELECT
pid,
max(DECODE(rn,1,NUM,NULL)) num1,
max(DECODE(rn,1,SUMGRADE,NULL)) SUMGRADE1,
max(DECODE(rn,2,NUM,NULL)) num2,
max(DECODE(rn,2,SUMGRADE,NULL)) SUMGRADE2,
max(DECODE(rn,3,NUM,NULL)) num3,
max(DECODE(rn,3,SUMGRADE,NULL)) SUMGRADE3,
max(DECODE(rn,4,NUM,NULL)) num4,
max(DECODE(rn,4,SUMGRADE,NULL)) SUMGRADE4
FROM TB
GROUP BY pid