You Asked
create table student
(
student_id int primary key,
student_name varchar2(100) not null
);
create table classes
(
class_id int primary key,
class_name varchar2(100) not null
);
create table student_class
(
student_id int references student,
class_id int references classes
);
1 student can be in many classes, 1 class can have many students.
student_class is the intersection table.
How can I write a query that, for a given fixed number of classes (or students), displays the students on the X-axis and the classes on the y-axis, and each "cell" in the resultset is a 1/0 depending on whether that student/class combination exists in the intersection table?
Thanks
and Tom said...
ops$tkyte@ORA9IR2> create table student
2 (
3 student_id int primary key,
4 student_name varchar2(30) not null
5 );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into student select user_id, username from all_users;
35 rows created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table classes
2 (
3 class_id int primary key,
4 class_name varchar2(3) not null
5 );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into classes
2 select rownum, 'c' || rownum
3 from dual
4 connect by level < 10;
9 rows created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table student_class
2 (
3 student_id int references student,
4 class_id int references classes,
5 primary key(student_id,class_id)
6 );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into student_class(student_id,class_id)
2 select student_id, class_id
3 from (
4 select student_id, class_id, dbms_random.value(0,1) rand, rownum r
5 from student, classes
6 )
7 where rand <= 0.3;
98 rows created.
assigned each student to about 1/3 of the classes randomly...
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into classes values ( 10, 'c10' );
1 row created.
wanted a class with NO students.... to test with...
we union all two queries together - one to get "column headings", that is the second statment below, we simply PIVOT the class name into columns from rows.
The first query does something similar, but it pivots the classes into columns BY STUDENT.
The OC column (order column) is there just to get the "column" heading as the first row of the result set...
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
2 from (
3 select 2 oc,
4 student_id,
5 max(decode(dr,1,'x')) c1,
6 max(decode(dr,2,'x')) c2,
7 max(decode(dr,3,'x')) c3,
8 max(decode(dr,4,'x')) c4,
9 max(decode(dr,5,'x')) c5,
10 max(decode(dr,6,'x')) c6,
11 max(decode(dr,7,'x')) c7,
12 max(decode(dr,8,'x')) c8,
13 max(decode(dr,9,'x')) c9,
14 max(decode(dr,10,'x')) c10
15 from (
16 select sc.student_id, c.class_id, dense_rank() over (order by c.class_id) dr
17 from student_class sc, classes c
18 where sc.class_id(+) = c.class_id
19 )
20 group by student_id
21 union all
22 select 1 oc,
23 to_number(null) student_id,
24 max( decode(r,1,class_name) ) c1,
25 max( decode(r,2,class_name) ) c2,
26 max( decode(r,3,class_name) ) c3,
27 max( decode(r,4,class_name) ) c4,
28 max( decode(r,5,class_name) ) c5,
29 max( decode(r,6,class_name) ) c6,
30 max( decode(r,7,class_name) ) c7,
31 max( decode(r,8,class_name) ) c8,
32 max( decode(r,9,class_name) ) c9,
33 max( decode(r,10,class_name) ) c10
34 from (
35 select class_name, row_number() over (order by class_id) r
36 from classes
37 )
38 )
39 order by oc, student_id
40 /
OC STUDENT_ID C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
---------- ---------- --- --- --- --- --- --- --- --- --- ---
1 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
2 0 x
2 5 x x x
2 11 x x x x
2 19 x x x
2 21 x
2 30 x x x x
2 31 x x
2 32 x x
2 33 x x x x
2 35 x x x x
2 36 x x
2 39 x x x x x
2 40 x x
2 42 x x
2 43 x x x
2 45 x x
2 47 x x x
2 48 x x x
2 49 x x x
2 50 x x
2 52 x x x
2 53 x x x
2 54 x x
2 55 x x x x x x
2 56 x x x
2 57 x
2 58 x x x
2 59 x x
2 60 x x x
2 62 x x x
2 66 x x x x
2 67 x x x x x
2 68 x x
2 70 x x x
2 x
36 rows selected.
ops$tkyte@ORA9IR2>
Rating
(7 ratings)
Is this answer out of date? If it is, please let us know via a Comment