如何动态取消透视

阿迪亚（Aditya Shrivastava）

我有一个这样的桌子报名-

Students                Class1               Class2                   Class3
student1                 1                      0                       1
student2                 0                      1                       0
student3                 1                      1                       0
studnet4                 0                      1                       1

我想这样输出-

Class1 has 3 Students

Class2 has 2 Students

Class3 has 3 Students

我已经这样查询了-

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。那么，如果有人可以帮助您呢？

笑

-- 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;
/

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。