Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE tab(NR NVARCHAR2(100), GRD NVARCHAR2(100), DT DATE); > > <pre> > ✓ > </pre> <!-- --> > INSERT INTO tab > SELECT '00000000000000000001' AS NR, N'06' AS GRD, DATE '2013-01-01' AS DT FROM dual UNION ALL > SELECT '00000000000000000001' ,N'06', DATE '2013-01-01' FROM dual UNION ALL > SELECT '00000000000000000001' ,N'21', DATE '2013-01-01' FROM dual UNION ALL > SELECT '00000000000000000002' ,N'06', DATE '2013-01-01' FROM dual UNION ALL > SELECT '00000000000000000002' ,N'21', DATE '2013-01-01' FROM dual UNION ALL > SELECT '00000000000000000004' ,N'01', DATE '2013-03-31' FROM dual; > > <pre> 6 rows affected > </pre> <!-- --> > CREATE TYPE tbl_array AS TABLE OF NVARCHAR2(4000); > > <pre> > ✓ > </pre> <!-- --> > select > nr , CAST(collect(grd) AS tbl_array) grds > from tab > group by nr > > <pre> > NR | GRDS > :- | :--- > </pre> <!-- --> > with prep as ( > select > nr , CAST(collect(grd) AS tbl_array) grds, ROW_NUMBER() OVER(ORDER BY nr) AS rn > from tab > group by nr > ) > select p.NR, p.grds, p2.grds as lead_grds > from prep p > LEFT JOIN prep p2 > ON p2.rn = p.rn +1; > > <pre> > NR | GRDS | LEAD_GRDS > :- | :--- | :-------- > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=17585a1f6b85691089e6c131f843ded7)*