Oracle SQL – Summarize data based on values in one column

Please review the following sample data

Could you please suggest an oracle sql query that can provide the following output

Thank you for your help.

>Solution :

WITH CTE(App_No,Dept,Status) AS
(
  SELECT 123,'IT','Pending' FROM DUAL UNION ALL
  SELECT 124,   'Sales' ,   'Closed' FROM DUAL UNION ALL
  SELECT 456,   'Sales' ,   'Declined' FROM DUAL UNION ALL
  SELECT 821,   'IT' ,  'Pending' FROM DUAL UNION ALL
  SELECT 221,   'IT' ,  'Closed' FROM DUAL UNION ALL
  SELECT 876,   'Sales' ,   'Pending' FROM DUAL UNION ALL
  SELECT 665,   'Sales' ,   'Closed' FROM DUAL
)
SELECT C.DEPT,
  SUM(CASE WHEN C.STATUS='Closed' THEN 1 ELSE 0 END) CLOSED,
  SUM(CASE WHEN C.STATUS='Declined' THEN 1 ELSE 0 END)DECLINED,
  SUM(CASE WHEN C.STATUS='Pending' THEN 1 ELSE 0 END)PENDING
  FROM CTE C
 GROUP BY C.DEPT
 ORDER BY C.DEPT

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=1d61ba161051cdff7ca7af622961ee2b