Oracle SQL - Summarize data based on values in one column-CodePudding

Please review the following sample data

App No	Dept	Status
123	IT	Pending
124	Sales	Closed
456	Sales	Declined
821	IT	Pending
221	IT	Closed
876	Sales	Pending
665	Sales	Closed

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

Dept	Closed	Pendng	Declined
IT	1	2	0
Sales	2	1	1

Thank you for your help.

CodePudding user response：

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