我如何以动态方式取消 [英] how do I unpivot dynmaically

问题描述

我有一张这样的表注册 -

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

我希望我这样输出 -



Class1有3名学生



Class2有2名学生



Class3有3名学生



我已经对此进行了查询 -

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;

但是如果每次我的in子句改变时，都会有更多的课程。我也不知道pl / sql。我尝试过使用但是我失败了。那么，如果有人可以提供帮助吗？

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?

推荐答案

解决方案是 -



创建或替换程序test_students
as



光标c_cols

是

select column_name

来自user_tab_columns

其中table_name ='STUDENTSENROLL'

和column_name！='学生';



l_number_of_students数字;

l_my_col user_tab_columns.column_name％type;

l_statement varchar2（30000）;



begin




$ c $ b
$ c $ b for c_cols循环中的r_cols



l_my_col：= r_cols.column_name;



l_statement：=

'选择总和（'|| l_my_col ||'）

来自studentsenroll' ;



立即执行l_statement到l_number_of_students;



dbms_output.put_line（'学生人数：'| | l_number_of_students ||'在课堂上：'|| l_my_col）;



结束循环;

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;

这篇关于我如何以动态方式取消的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！