1 2 3 4 5 6 7 8 9 10 | WITH T AS ( SELECT 'A' A, '1' B,150 C,100 D FROM DUAL UNION ALL SELECT 'A' , '2' ,150 ,50 FROM DUAL UNION ALL SELECT 'B' , '1' ,200 ,80 FROM DUAL UNION ALL SELECT 'B' , '2' ,200 ,100 FROM DUAL UNION ALL SELECT 'B' , '3' ,200 ,20 FROM DUAL ) SELECT A , B, DECODE(RN,1,C) C , D FROM ( SELECT T.* , ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) RN FROM T ) |
1 2 3 4 5 6 7 8 9 10 11 | WITH T AS ( SELECT 'A' A, '1' B,150 C,100 D FROM DUAL UNION ALL SELECT 'A' , '2' ,150 ,50 FROM DUAL UNION ALL SELECT 'B' , '1' ,200 ,80 FROM DUAL UNION ALL SELECT 'B' , '2' ,200 ,100 FROM DUAL UNION ALL SELECT 'B' , '3' ,200 ,20 FROM DUAL ) SELECT CASE WHEN A=(LAG(A) OVER ( ORDER BY A,B)) THEN NULL ELSE A END AA , CASE WHEN C=(LAG(C) OVER ( ORDER BY A,B)) THEN NULL ELSE C END CC , B , D FROM T |