I have an Oracle 12c table that looks something like this:
Name Class Type
--------------------------
Alice Math S
Alice Female A
Bob Anthropology S
Bob Male A
Charlie Science S
Charlie Tennis A
Charlie Male A
Do Math S
Do Female A
Do Tennis A
Elmer Male A
Elmer Science S
I would like to pivot the table to produce a report that looks like below. Classes of type "S" are rows, and type "A" are columns. And then count the number of people that fall into each category. For example, there are two people that are both Female and take class Math - Alice and Do.
Female Male Tennis Total
-----------------------------------------------
Math 2 0 1 3
Anthropology 0 1 0 1
Science 0 2 1 3
Total 2 3 2 7
I am familiar with pivot and cube in Oracle but I am stuck with getting the Class field split between rows and columns in the report.
I would really appreciate your help.
You can use conditional aggregation as following:
SQL> with dataa(name, class, type) as 2 ( 3 select 'Alice', 'Math' ,'S' from dual union all 4 select 'Alice', 'Female' ,'A' from dual union all 5 select 'Bob', 'Anthropology' ,'S' from dual union all 6 select 'Bob', 'Male' ,'A' from dual union all 7 select 'Charlie', 'Science' ,'S' from dual union all 8 select 'Charlie', 'Tennis' ,'A' from dual union all 9 select 'Charlie', 'Male' ,'A' from dual union all 10 select 'Do', 'Math' ,'S' from dual union all 11 select 'Do', 'Female' ,'A' from dual union all 12 select 'Do', 'Tennis' ,'A' from dual union all 13 select 'Elmer', 'Male' ,'A' from dual union all 14 select 'Elmer', 'Science' ,'S' from dual 15 ), 16 -- your query starts from here 17 CTE AS 18 ( 19 SELECT S.CLASS AS CLASS, 20 COALESCE(SUM(CASE WHEN A.CLASS = 'Female' THEN 1 END),0) AS Female, 21 COALESCE(SUM(CASE WHEN A.CLASS = 'Male' THEN 1 END),0) AS Male, 22 COALESCE(SUM(CASE WHEN A.CLASS = 'Tennis' THEN 1 END),0) AS Tennis, 23 COALESCE(SUM(CASE WHEN A.CLASS IN ('Female','Male','Tennis') THEN 1 END),0) AS TOTAL 24 FROM DATAA S JOIN DATAA A ON (A.name = S.name) 25 WHERE S.type = 'S' AND A.TYPE = 'A' 26 GROUP BY S.CLASS 27 ) 28 SELECT CLASS, Female, Male, Tennis, TOTAL FROM CTE 29 UNION ALL 30 SELECT 'Total' AS CLASS, SUM(Female), SUM(Male), SUM(Tennis), SUM(TOTAL) FROM CTE; CLASS FEMALE MALE TENNIS TOTAL ------------ ---------- ---------- ---------- ---------- Science 0 2 1 3 Anthropology 0 1 0 1 Math 2 0 1 3 Total 2 3 2 7 SQL>
Cheers!!