CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Anil
Anil

Reputation: 49

Unable to PIVOT on ORACLE 10G DATABASE

I'm trying to pivot with oracle 10G. Screen shot1 is the actual data in table. I'm trying to get data like screen shot 2. Please suggest me PIVOT function is not working in 10G. Screen shot1.Screen Shot2

Upvotes: 2

Views: 336

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

You could use conditional aggregation:

SELECT ANALYST
    ,COUNT(CASE WHEN status = 'unchecked'   THEN 1 END) AS unchecked
    ,COUNT(CASE WHEN status = 'observation' THEN 1 END) AS observation
    ,COUNT(CASE WHEN status = 'supervisor'  THEN 1 END) AS supervisor
    ,COUNT(CASE WHEN status = 'pending'     THEN 1 END) AS pending
    ,COUNT(CASE WHEN status = 'closed'      THEN 1 END) AS closed
    ,COUNT(*) AS Grand_Tot
FROM tab
GROUP BY ANALYST
ORDER BY ANALYST;

EDIT:

Can i know how to SUM the Grand_Tot Column along with that query i was trying SUM(Grand_Tot) but getting error as invalid identifier.

One way is to use subquery:

SELECT sub.*, SUM(Grand_Tot) OVER() AS Sum_Grant_Tot
FROM (
    SELECT ANALYST
        ,COUNT(CASE WHEN status = 'unchecked'   THEN 1 END) AS unchecked
        ,COUNT(CASE WHEN status = 'observation' THEN 1 END) AS observation
        ,COUNT(CASE WHEN status = 'supervisor'  THEN 1 END) AS supervisor
        ,COUNT(CASE WHEN status = 'pending'     THEN 1 END) AS pending
        ,COUNT(CASE WHEN status = 'closed'      THEN 1 END) AS closed
        ,COUNT(*) AS Grand_Tot
    FROM tab
    GROUP BY ANALYST
) sub
ORDER BY ANALYST;

Upvotes: 3

Related Questions