WITH my_tab AS (SELECT 1 AS daynum, 'CR' AS trantype, 200 AS amount FROM dual UNION ALL SELECT 1 AS daynum, 'DR' AS trantype, -300 AS amount FROM dual UNION ALL SELECT 1 AS daynum, 'CR' AS trantype, 400 AS amount FROM dual UNION ALL SELECT 1 AS daynum, 'DR' AS trantype, -500 AS amount FROM dual UNION ALL SELECT 2 AS daynum, 'CR' AS trantype, 800 AS amount FROM dual UNION ALL SELECT 2 AS daynum, 'DR' AS trantype, -200 AS amount FROM dual UNION ALL SELECT 3 AS daynum, 'CR' AS trantype, 900 AS amount FROM dual UNION ALL SELECT 3 AS daynum, 'DR' AS trantype, -500 AS amount FROM dual UNION ALL SELECT 4 AS daynum, 'CR' AS trantype, 900 AS amount FROM dual UNION ALL SELECT 5 AS daynum, 'CR' AS trantype, 900 AS amount FROM dual UNION ALL SELECT 5 AS daynum, 'CR' AS trantype, 200 AS amount FROM dual UNION ALL SELECT 5 AS daynum, 'DR' AS trantype, -500 AS amount FROM dual UNION ALL SELECT 6 AS daynum, 'DR' AS trantype, -200 AS amount FROM dual), dummy AS (SELECT 1 id, 'Opening Balance' descr FROM dual UNION ALL SELECT 2 id, 'CR' descr FROM dual UNION ALL SELECT 3 id, 'DR' descr FROM dual UNION ALL SELECT 4 id, 'Closing Balance' descr FROM dual) SELECT mt.daynum, d.descr, mt.trantype, decode(d.id, 1, 0, 2, amount, 3, amount, 4, SUM(decode(d.id, 1, 0, 4, 0, amount)) OVER (partition BY mt.daynum)amount FROM   (SELECT mt1.*, ROW_NUMBER() OVER (partition BY mt1.daynum ORDER BY mt1.trantype) rn FROM   my_tab mt1) mt LEFT OUTER JOIN dummy d partition BY (id, descr) ON (mt.trantype = d.descr) WHERE  CASE WHEN d.id = 1 AND rn = 1 THEN 1 WHEN d.id = 2 AND mt.trantype = 'CR' THEN 1 WHEN d.id = 3 AND mt.trantype = 'DR' THEN 1 WHEN d.id = 4 AND rn = 1 THEN 1 ELSE 0 END = 1 ORDER BY mt.daynum, d.id;