how do I unpivot dynmaically

Question

3.50/5 (2 votes)

See more:

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-

SQL

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.I have tried it using but I am failing. So if anyone can help?

Posted 13-Aug-15 18:51pm

Member 11898509

Updated 14-Aug-15 2:40am

User 11060979

v5

Add a Solution

Comments

Richard Deeming 14-Aug-15 9:44am

That looks like a very bad database design.

You should have three tables: students, classes, and enrolment. The enrolment table would just contain the primary key of a student and the primary key of a class, indicating that the student was enrolled in that class.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11898509 · Accepted Answer · 2015-08-14T04:42:00

Solution is-

create or replace procedure test_students
as

cursor c_cols
is
select column_name
from user_tab_columns
where table_name = 'STUDENTSENROLL'
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 studentsenroll ';

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;