Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 26, 2005 - 8:57 pm UTC

Last updated: December 04, 2009 - 5:35 pm UTC

Version: 9.2.0.6

Viewed 1000+ times

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

Comments

Another solution

Michel Cadot, November 27, 2005 - 2:34 pm UTC

Hi Tom,

Here's another solution without knowing a priori the number of classes and without the extra line for the class without any student.

SQL> create table students (student_id int primary key, student_name varchar2(30) not null);

Table created.

SQL> insert into students 
  2  select distinct a.user_id+b.user_id, 'user'||to_char(a.user_id+b.user_id,'fm00')
  3  from all_users a, all_users b;

20 rows created.

SQL> select * from students;
         0 user00
         5 user05
        10 user10
        11 user11
        16 user16
        19 user19
        22 user22
        24 user24
        29 user29
        30 user30
        34 user34
        35 user35
        38 user38
        40 user40
        41 user41
        48 user48
        49 user49
        58 user58
        59 user59
        60 user60

20 rows selected.

SQL> create table classes (class_id int primary key, class_name varchar2(3) not null);

Table created.

SQL> insert into classes select rownum, 'c'||to_char(rownum,'fm00') 
  2  from dual 
  3  connect by level < 10;

9 rows created.

SQL> select * from classes;
         1 c01
         2 c02
         3 c03
         4 c04
         5 c05
         6 c06
         7 c07
         8 c08
         9 c09

9 rows selected.

SQL> create table student_class (
  2    student_id int references students,
  3    class_id int references classes,
  4    primary key(student_id,class_id));

Table created.

SQL> insert into student_class(student_id,class_id)
  2  select student_id, class_id
  3  from ( select student_id, class_id, dbms_random.value(0,1) rand, rownum r
  4         from students, classes )
  5  where rand <= 0.3;

57 rows created.

SQL> insert into classes values (10, 'c10');

1 row created.

SQL> commit;

Commit complete.

SQL> col nop noprint
SQL> col sid format a10
SQL> col cln format a50
SQL> def max_class_lg=4
SQL> set head off
SQL> select 1 nop, 'Student Id' sid,
  2         translate(max(substr(sys_connect_by_path(class_name,' '),2)),'#',' ') cln
  3  from ( select rpad(class_name,&max_class_lg,'#') class_name,
  4         row_number () over (order by class_id) curr,
  5         row_number () over (order by class_id) - 1 prev
  6         from classes  )
  7  connect by prior curr = prev
  8  start with curr = 1
  9  union all
 10  select 2 nop, '----------' sid,
 11         max(substr(sys_connect_by_path(rpad('-',length(class_name),'-'),' '),2))
 12           cln
 13  from ( select rpad(class_name,&max_class_lg,'#') class_name,
 14         row_number () over (order by class_id) curr,
 15         row_number () over (order by class_id) - 1 prev
 16         from classes  )
 17  connect by prior curr = prev
 18  start with curr = 1
 19  union all
 20  select 3 nop, lpad(to_char(student_id),10) sid,
 21         translate(max(substr(sys_connect_by_path(present,' '),2)),'#',' ') cln
 22  from ( select student_id, class_id, 
 23                rpad(nvl(present,'#'),&max_class_lg,'#') present,
 24         row_number () 
 25           over (partition by student_id order by class_id) curr,
 26         row_number () 
 27           over (partition by student_id order by class_id) - 1 prev
 28         from ( select s.student_id, c.class_id, 
 29                (select 'x' from student_class sc
 30                 where sc.student_id = s.student_id
 31                   and sc.class_id = c.class_id ) present
 32                from students s, classes c ) )
 33  connect by prior curr = prev and prior student_id = student_id
 34  start with curr = 1
 35  group by student_id
 36  order by 1, 2
 37  /
Student Id c01  c02  c03  c04  c05  c06  c07  c08  c09  c10
---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
         0                x    x
         5      x                        x    x    x
        10 x    x                        x
        11      x              x                   x
        16                               x         x
        19                x                        x
        22 x
        24      x    x    x    x    x
        29           x              x              x
        30 x         x
        34 x                                       x
        35      x         x              x    x
        38                          x         x    x
        40 x                        x    x         x
        41                          x    x
        48 x    x                   x
        49      x                             x    x
        58 x    x                   x         x    x
        59      x
        60           x         x         x

22 rows selected.

Regards
Michel 

Tom Kyte
November 27, 2005 - 4:25 pm UTC

neat idea.

Thanks!

VA, November 27, 2005 - 5:47 pm UTC

Thanks lot to both of you. Very very clever solutions.

Please explain

A reader, November 27, 2005 - 5:53 pm UTC

Tom, I am not sure I understand your solution. How does the dense_rank() result in showing the X where the cell exists in the intersection table? Could you please break it down and explain how it works?

Michel: Your solution is even more black magic, please explain it in detail, if you dont mind. Thanks.

Tom Kyte
November 27, 2005 - 6:28 pm UTC

run the queries from the inside out without the aggregation to see how they "build" on themselves.

I am using dense_rank() to assign the numbers 1, 2, 3, 4, ... 10 (upto 10 in this case) in order to pivot them.

Sort the data by class_id, assign the number 1 to the lowest class_id, 2 to the second lowest and so on.

Commentary

Michel Cadot, November 28, 2005 - 4:02 am UTC

I can explain my query if Tom doesn't mind.

It is build on 3 queries: the first one displays the header, the second one the '-' line and the last one the table.

As they are both on the same pattern, i'll explain only the first one.
The principle is to create a pseudo-hierarchy between the rows: the previous one is the parent of the following one. This is the purpose of the row_number function to assign a number to each row and its preceding one:

SQL> def max_class_lg=4
SQL> l
  1  select rpad(class_name,&max_class_lg,'#') class_name,
  2  row_number () over (order by class_id) curr,
  3  row_number () over (order by class_id) - 1 prev
  4* from classes
SQL> /
CLAS       CURR       PREV
---- ---------- ----------
c01#          1          0
c02#          2          1
c03#          3          2
c04#          4          3
c05#          5          4
c06#          6          5
c07#          7          6
c08#          8          7
c09#          9          8

9 rows selected.

Now each row has its number and the number of its "parent". Why i padded with # is just for display purpose (we'll see that later).

Then i use the sys_connect_by_path to buid the parent/child relation string of each row.

SQL> select sys_connect_by_path(class_name,' ') cln
  2  from ( select rpad(class_name,&max_class_lg,'#') class_name,
  3                row_number () over (order by class_id) curr,
  4                row_number () over (order by class_id) - 1 prev
  5         from classes )
  6  connect by prior curr = prev
  7  start with curr = 1
  8  /
CLN
-----------------------------------------------
 c01#
 c01# c02#
 c01# c02# c03#
 c01# c02# c03# c04#
 c01# c02# c03# c04# c05#
 c01# c02# c03# c04# c05# c06#
 c01# c02# c03# c04# c05# c06# c07#
 c01# c02# c03# c04# c05# c06# c07# c08#
 c01# c02# c03# c04# c05# c06# c07# c08# c09#

9 rows selected.

Only the last one, giving all the classes is interesting, this is the purpose of the max function. 
Now the complement is for display purpose: "substr( ,2)" to delete the first ' ', translate(...) to convert all '#' to ' ', sid column for heading the student_id column, nop to order the result of the 3 queries in union all.

SQL> select 1 nop, 'Student Id' sid,
  2         translate(max(substr(sys_connect_by_path(class_name,' '),2)),'#',' ') cln
  3  from ( select rpad(class_name,&max_class_lg,'#') class_name,
  4         row_number () over (order by class_id) curr,
  5         row_number () over (order by class_id) - 1 prev
  6         from classes  )
  7  connect by prior curr = prev
  8  start with curr = 1
  9  /
       NOP SID        CLN
---------- ---------- --------------------------------------------------
         1 Student Id c01  c02  c03  c04  c05  c06  c07  c08  c09

1 row selected.

The # were there because sys_connect_by_path cannot have a separator character (its second parameter) that is in the values of the connected field.

The second query is exactly the same but i display a '-' set instead of class name (this is "rpad('-',length(class_name),'-')").

The third query is build in the same way upon the core query:

select s.student_id, c.class_id, 
       ( select 'x' from student_class sc
         where sc.student_id = s.student_id
               and sc.class_id = c.class_id ) present
from students s, classes c

which generates a row for each student and each class with, in third column, 'x' if the student follows this class and null else.

Regards
Michel
 

Helena Marková, November 28, 2005 - 4:11 am UTC


Show the CLN values in single columns (select by Michel)

Werner, August 03, 2007 - 9:57 am UTC

Michel,

I have a question regarding your select. Is it possible to show the CLN values in single columns like with the select by Tom.

Childs grouping

Claudiu, December 04, 2009 - 1:45 pm UTC

create table links
(parent varchar2(10),
child varchar2(20)
)
/

Insert into LINKS
(PARENT, CHILD)
Values
('A', 'C1')
/
Insert into LINKS
(PARENT, CHILD)
Values
('A', 'C2')
/
Insert into LINKS
(PARENT, CHILD)
Values
('A', 'C3')
/
Insert into LINKS
(PARENT, CHILD)
Values
('B', 'C1')
/
Insert into LINKS
(PARENT, CHILD)
Values
('B', 'C5')
/
Insert into LINKS
(PARENT, CHILD)
Values
('C', 'C8')
/
Insert into LINKS
(PARENT, CHILD)
Values
('C', 'C9')
/
Insert into LINKS
(PARENT, CHILD)
Values
('D', 'C5')
/
Commit
/

How can I get the following result:

GROUP PARENT
1 A
1 B
1 D
2 C

A,B,D are in the same group because are child linked.
C is in the group 2 because is not linked with other parent

Have a nice day
Tom Kyte
December 04, 2009 - 5:35 pm UTC

insufficiently spec'ed out here - what do "child linked" mean precisely.

there must be lots and lots of rules you have with this data - what are they all.

why is c number 2, why isn't c number 1 and a,b,d number 2.

where is the logic here - what is the algorithm to tie it all together.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.