I have a table enroll like this-
Students Class1 Class2 Class3
student1 1 0 1
student2 0 1 0
student3 1 1 0
studnet4 0 1 1
And I want I output like this-
Class1 has 3 Students
Class2 has 2 Students
Class3 has 3 Students
I have made a query for that like this-
select classname||' has '||count(num)||' students 'as no_of_students from
(
select * from enroll )
unpivot (
num for classname in (class1,class2,class3)
)
where num=1
group by classname;
But if there are more class than every time I had change in my in clause. I don't know pl/sql also. So if anyone can help?
-- it is an old-school solution, but it worked for me
-- step 1
create or replace procedure test_students
as
cursor c_cols
is
select column_name
from user_tab_columns
where table_name = 'ENROLL'
and column_name != 'STUDENTS';
l_number_of_students number;
l_my_col user_tab_columns.column_name%type;
l_statement varchar2(30000);
begin
for r_cols in c_cols loop
l_my_col := r_cols.column_name;
l_statement :=
' select sum('||l_my_col||')
from enroll ';
execute immediate l_statement into l_number_of_students;
dbms_output.put_line ('Number of students: '||l_number_of_students ||'in Class :'||l_my_col);
end loop;
end;
-- step 2
begin
test_students;
end;
/