WITH t AS ( SELECT 'a' cd, 100 amt FROM dual UNION ALL SELECT 'a', 200 FROM dual UNION ALL SELECT 'a', 150 FROM dual UNION ALL SELECT 'b', 200 FROM dual UNION ALL SELECT 'b', 150 FROM dual UNION ALL SELECT 'c', 180 FROM dual UNION ALL SELECT 'c', 200 FROM dual ) -- 1. Group By, Decode SELECT COUNT(DECODE(cd, 'a', 1)) a , COUNT(DECODE(cd, 'b', 1)) b , COUNT(DECODE(cd, 'c', 1)) c FROM t ; -- 2. Pivot SELECT * FROM t PIVOT (COUNT(amt) FOR cd IN ('a' a, 'b' b, 'c' c)) ;