Oracle SQL Pivot Usage

Sometimes we might have requirements to get the results through SQL for a kind of upside down way. In Oracle, they have added this feature using a keyword called pivot. Lets take a simple example as follows.

Assume we have a simple table for employees with categories

SELECT * FROM t_employee;

Now if we get the counts group by the category, it will be as follows

SELECT emp.category,COUNT(*) AS total FROM t_employee emp GROUP BY category ;

Now using pivot, you can get the above result as another table kind of as follows

SELECT *

FROM

  (SELECT emp.category,COUNT(*) AS total FROM t_employee emp GROUP BY category

  ) pivot ( SUM(total) FOR category IN ('Dev' AS DEV,'QA' AS QA,'Mgmt' AS MANAGEMENT)) ;