Column title with column header and counter / pivot based on column label in another table

Question

I have two tables:

TABLE LIST:

student_id  class_id    subject_1   subject_2   subject_3   subject_4   subject_5
----------- ----------- ----------- ----------- ----------- ----------- ----------
1           9           78          87                                  95
2           9           67          95                                    87
3           9           85          84                                    85
4           10          70                      65          78    
5           10          75                      80          81    
6           10          80                      75          82    

Table subject_names

column_name     subject_name
--------------- -------------
subject_1       English
subject_2       Chemistry
subject_3       Economics
subject_4       Accounts
subject_5       Biology

Now I need to create such a report for class_id = 9

column_name     subject_name  no_of_students
--------------- ------------- --------------
subject_1       English       3
subject_2       Chemistry     3
subject_3       Economics     0
subject_4       Accounts      0
subject_5       Biology       3

In short, I have to generate a report with the column names, subject_name and the number of students from class_id = 9 (or 10, independently) that appeared for this object.

All I managed to do is

1.

    SELECT sn.column_name, sn.subject_name FROM subject_names sn;

and

2.

    SELECT ml.class_id,
           count(ml.subject_1) AS s1,
           count(ml.subject_2) AS s1,
           count(ml.subject_3) AS s1,
           count(ml.subject_4) AS s1,
           count(ml.subject_5) AS s1,
      FROM marklist ml
     WHERE ml.class_id = 9;

I do not understand how I can go forward and rotate the results of query 2 using the query. I can go in the wrong direction, but I have no idea.

Answer 1

I have found the answer. You can skip CTE and check the main request in the following:

/* -- This WITH clause is just for your reference.
WITH marklist AS (
SELECT 1 student_id, 9 class_id, 78 subject_1,87 subject_2,
       null subject_3, null subject_4, 95 subject_5 FROM dual
UNION ALL SELECT 2, 9, 67,95, null, null, 87 FROM dual
UNION ALL SELECT 3, 9, 85,84, null, null, 85 FROM dual
UNION ALL SELECT 4, 10,70, null, 65,78, null FROM dual
UNION ALL SELECT 5, 10,75, null, 80,81, null FROM dual
UNION ALL SELECT 6, 10,80, null, 75,82, null FROM dual
)
, subject_names AS (
SELECT 'subject_1' column_name, 'English' subject_name FROM dual
UNION ALL SELECT 'subject_2', 'Chemistry' FROM dual
UNION ALL SELECT 'subject_3', 'Economics' FROM dual
UNION ALL SELECT 'subject_4', 'Accounts' FROM dual
UNION ALL SELECT 'subject_5', 'Biology' FROM dual
) -- */
SELECT sn.column_name, sn.subject_name,
       (SELECT COUNT(CASE sn.column_name
                        WHEN 'subject_1' THEN ml.subject_1
                        WHEN 'subject_2' THEN ml.subject_2
                        WHEN 'subject_3' THEN ml.subject_3
                        WHEN 'subject_4' THEN ml.subject_4
                        WHEN 'subject_5' THEN ml.subject_5
                      END)
           FROM marklist ml
          WHERE ml.class_id = 9) AS no_of_students
  FROM subject_names sn;

OUTPUT:

column_name    subject_name    no_of_students
-------------- --------------- --------------
subject_1      English         3
subject_2      Chemistry       3
subject_3      Economics       0
subject_4      Accounts        0
subject_5      Biology         3

Answer 2

unpivot subject_names.

with unpivot_x(student_id,class_id,subject_code,marks) as (
    select * from marklist
    unpivot (marks for subject_code in (  subject_1 as 'subject_1',
                                          subject_2 as 'subject_2',
                                          subject_3 as 'subject_3',
                                          subject_4 as 'subject_4',
                                          subject_5 as 'subject_5'
                                      )
                                      ))
select a.column_name,a.subject_name, count(b.student_id)
  from subject_names a left outer join unpivot_x b
    on a.column_name = b.subject_code and b.class_id = 9
 group by a.column_name,a.subject_name
 order by 1;

sqlfiddle.

Answer 3

try it

SELECT a.column_name, a.subject_name, COUNT( subject_1 ) 
FROM subject_names a
JOIN mark_list b ON b.class_id =9
AND a.subject_name =  'English'
GROUP BY a.subject_name
UNION ALL SELECT a.column_name columnname, a.subject_name, COUNT( subject_3 ) 
FROM subject_names a
JOIN mark_list b ON b.class_id =9
AND a.subject_name =  'Chemistry'
GROUP BY a.subject_name
UNION ALL SELECT a.column_name columnname, a.subject_name, COUNT( subject_4 ) 
FROM subject_names a
JOIN mark_list b ON b.class_id =9
AND a.subject_name =  'Economics'
GROUP BY a.subject_name UNION ALL 
SELECT a.column_name columnname, a.subject_name, COUNT( subject_2 ) 
FROM subject_names a
JOIN mark_list b ON b.class_id =9
AND a.subject_name =  'Accounts'
GROUP BY a.subject_name
UNION ALL 
SELECT a.column_name columnname, a.subject_name, COUNT( subject_1 ) 
FROM subject_names a
JOIN mark_list b ON b.class_id =9
AND a.subject_name =  'Biology'
GROUP BY a.subject_name