Skip to Main Content
  • Questions
  • To report Rooms By Periods By Student

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Venkat.

Asked: October 03, 2022 - 4:47 pm UTC

Last updated: November 11, 2022 - 3:23 am UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0

Viewed 1000+ times

You Asked

Hello-

Required to report room numbers for every period of the day the student is in (like a sort of pivot format).

I have the query that pulls the required information:

--
Select
s.schoolid scid,
s.student_number stnu ,
s.lastfirst na,
cc.expression,
sec.room rm , 
s.grade_level grl
from students s, sections sec,  cc cc 
where
s.id = cc.studentid and
cc.sectionid = sec.id and
sec.schoolid = 87  and 
cc.termid =   3200


Here is the sample of 5 student records returned by the above:


SCID STNU NA EXPRESSION RM GRL
87 100 AAA 3-5(A-E) E003 12
87 100 AAA 9(A-E) Patti12 12
87 100 AAA 6(A-E) E207B 12
87 100 AAA 8(A-E) E002B 12
87 100 AAA 9(A-E) PattiAll 12
87 100 AAA 7(A-E) ECaf 12
87 100 AAA 2(A-E) EGym 12
87 100 AAA 1(A-E) E002B 12
87 101 BBB 9(A-E) MularzAll 12
87 101 BBB 1(A-E) E201 12
87 101 BBB 7-8(A-E)  12
87 101 BBB 2(A-E) EGym 12
87 101 BBB 6(A-E) ECaf 12
87 101 BBB 3-5(A-E) E003 12
87 102 CCC 8(A-E)  12
87 102 CCC 2(A-E) E200 12
87 102 CCC 6(A-E) E209 12
87 102 CCC 1(A-E) EGym 12
87 102 CCC 7(A-E) ECaf 12
87 102 CCC 9(A-E)  12
87 102 CCC 3-5(A-E) E008 12
87 103 DDD 3-5(A-E) E106B 12
87 103 DDD 2(A-E) E207A 12
87 103 DDD 8(A-E)  12
87 103 DDD 6(A-E) E209 12
87 103 DDD 1(A-E) EGym 12
87 103 DDD 7(A-E) ECaf 12
87 103 DDD 9(A-E)  12
87 104 EEE 1(A-E) EGym 12
87 104 EEE 8(A-E) E208A 12
87 104 EEE 6(A-E) E207B 12
87 104 EEE 7(A-E) E306 12
87 104 EEE 2(A-E) E208A 12
87 104 EEE 5(A-E) ECaf 12
87 104 EEE 3(A-E) E110 12
87 104 EEE 4(A-E) E001B 12



Expect to have an out put like so:


STNU NA GRL Per 1 Per 2 Per 3 Per 4 Per 5 Per 6 Per 7 Per 8 Per 9 Per 10 Per 11 Per 12
100 AAA 12 E002B EGym E003 E003 E003 E207B ECaf E002B PattiAll   
101 BBB 12 E201 EGym E003 E003 E003 ECaf   MularzAll   
102 CCC 12 EGym E200 E008 E008 E008 E209 ECaf     
103 DDD 12 EGym E207A E106B E106B E106B E209 ECaf     
104 EEE 12 EGym E208A E110 E001B ECaf E207B E306 E208A 



A student can attend a max of 12 periods in a day.

Student can attend a two period class (Ex:7-8(A-E)) or a three period class (Ex:3-5(A-E)) or a single period class (Ex: 1(A-E))

Thanks for any assistance!

Venkat



--===========

create table t 
( 
SCID            number (4) ,
STNU            number(8), 
NA         varchar2(30),
EXPRESSION varchar2(10),
RM         varchar2(10),
GRL             number(2) 
) ;




insert into t values ( 87,100,'AAA','3-5(A-E)','E003',12);
insert into t values ( 87,100,'AAA','9(A-E)','Patti12',12);
insert into t values ( 87,100,'AAA','6(A-E)','E207B',12);
insert into t values ( 87,100,'AAA','8(A-E)','E002B',12);
insert into t values ( 87,100,'AAA','9(A-E)','PattiAll',12);
insert into t values ( 87,100,'AAA','7(A-E)','ECaf',12);
insert into t values ( 87,100,'AAA','2(A-E)','EGym',12);
insert into t values ( 87,100,'AAA','1(A-E)','E002B',12);
insert into t values ( 87,101,'BBB','9(A-E)','MularzAll',12);
insert into t values ( 87,101,'BBB','1(A-E)','E201',12);
insert into t values ( 87,101,'BBB','7-8(A-E)','',12);
insert into t values ( 87,101,'BBB','2(A-E)','EGym',12);
insert into t values ( 87,101,'BBB','6(A-E)','ECaf',12);
insert into t values ( 87,101,'BBB','3-5(A-E)','E003',12);
insert into t values ( 87,102,'CCC','8(A-E)','',12);
insert into t values ( 87,102,'CCC','2(A-E)','E200',12);
insert into t values ( 87,102,'CCC','6(A-E)','E209',12);
insert into t values ( 87,102,'CCC','1(A-E)','EGym',12);
insert into t values ( 87,102,'CCC','7(A-E)','ECaf',12);
insert into t values ( 87,102,'CCC','9(A-E)','',12);
insert into t values ( 87,102,'CCC','3-5(A-E)','E008',12);
insert into t values ( 87,103,'DDD','3-5(A-E)','E106B',12);
insert into t values ( 87,103,'DDD','2(A-E)','E207A',12);
insert into t values ( 87,103,'DDD','8(A-E)','',12);
insert into t values ( 87,103,'DDD','6(A-E)','E209',12);
insert into t values ( 87,103,'DDD','1(A-E)','EGym',12);
insert into t values ( 87,103,'DDD','7(A-E)','ECaf',12);
insert into t values ( 87,103,'DDD','9(A-E)','',12);
insert into t values ( 87,104,'EEE','1(A-E)','EGym',12);
insert into t values ( 87,104,'EEE','8(A-E)','E208A',12);
insert into t values ( 87,104,'EEE','6(A-E)','E207B',12);
insert into t values ( 87,104,'EEE','7(A-E)','E306',12);
insert into t values ( 87,104,'EEE','2(A-E)','E208A',12);
insert into t values ( 87,104,'EEE','5(A-E)','ECaf',12);
insert into t values ( 87,104,'EEE','3(A-E)','E110',12);
insert into t values ( 87,104,'EEE','4(A-E)','E001B',12);



expected output:

STNU NA GRL Per 1 Per 2 Per 3 Per 4 Per 5 Per 6 Per 7 Per 8 Per 9 Per 10 Per 11 Per 12
100 AAA 12 E002B EGym E003 E003 E003 E207B ECaf E002B PattiAll   
101 BBB 12 E201 EGym E003 E003 E003 ECaf   MularzAll   
102 CCC 12 EGym E200 E008 E008 E008 E209 ECaf     
103 DDD 12 EGym E207A E106B E106B E106B E209 ECaf     
104 EEE 12 EGym E208A E110 E001B ECaf E207B E306 E208A 








and Connor said...

So we'll build this up step by step


SQL>
SQL> create table t
  2  (
  3  SCID            number (4) ,
  4  STNU            number(8),
  5  NA         varchar2(30),
  6  EXPRESSION varchar2(10),
  7  RM         varchar2(10),
  8  GRL             number(2)
  9  ) ;

Table created.

SQL>
SQL> insert into t values ( 87,100,'AAA','3-5(A-E)','E003',12);

1 row created.

SQL> insert into t values ( 87,100,'AAA','9(A-E)','Patti12',12);

1 row created.

SQL> insert into t values ( 87,100,'AAA','6(A-E)','E207B',12);

1 row created.

SQL> insert into t values ( 87,100,'AAA','8(A-E)','E002B',12);

1 row created.

SQL> insert into t values ( 87,100,'AAA','9(A-E)','PattiAll',12);

1 row created.

SQL> insert into t values ( 87,100,'AAA','7(A-E)','ECaf',12);

1 row created.

SQL> insert into t values ( 87,100,'AAA','2(A-E)','EGym',12);

1 row created.

SQL> insert into t values ( 87,100,'AAA','1(A-E)','E002B',12);

1 row created.

SQL> insert into t values ( 87,101,'BBB','9(A-E)','MularzAll',12);

1 row created.

SQL> insert into t values ( 87,101,'BBB','1(A-E)','E201',12);

1 row created.

SQL> insert into t values ( 87,101,'BBB','7-8(A-E)','',12);

1 row created.

SQL> insert into t values ( 87,101,'BBB','2(A-E)','EGym',12);

1 row created.

SQL> insert into t values ( 87,101,'BBB','6(A-E)','ECaf',12);

1 row created.

SQL> insert into t values ( 87,101,'BBB','3-5(A-E)','E003',12);

1 row created.

SQL> insert into t values ( 87,102,'CCC','8(A-E)','',12);

1 row created.

SQL> insert into t values ( 87,102,'CCC','2(A-E)','E200',12);

1 row created.

SQL> insert into t values ( 87,102,'CCC','6(A-E)','E209',12);

1 row created.

SQL> insert into t values ( 87,102,'CCC','1(A-E)','EGym',12);

1 row created.

SQL> insert into t values ( 87,102,'CCC','7(A-E)','ECaf',12);

1 row created.

SQL> insert into t values ( 87,102,'CCC','9(A-E)','',12);

1 row created.

SQL> insert into t values ( 87,102,'CCC','3-5(A-E)','E008',12);

1 row created.

SQL> insert into t values ( 87,103,'DDD','3-5(A-E)','E106B',12);

1 row created.

SQL> insert into t values ( 87,103,'DDD','2(A-E)','E207A',12);

1 row created.

SQL> insert into t values ( 87,103,'DDD','8(A-E)','',12);

1 row created.

SQL> insert into t values ( 87,103,'DDD','6(A-E)','E209',12);

1 row created.

SQL> insert into t values ( 87,103,'DDD','1(A-E)','EGym',12);

1 row created.

SQL> insert into t values ( 87,103,'DDD','7(A-E)','ECaf',12);

1 row created.

SQL> insert into t values ( 87,103,'DDD','9(A-E)','',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','1(A-E)','EGym',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','8(A-E)','E208A',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','6(A-E)','E207B',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','7(A-E)','E306',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','2(A-E)','E208A',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','5(A-E)','ECaf',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','3(A-E)','E110',12);

1 row created.

SQL> insert into t values ( 87,104,'EEE','4(A-E)','E001B',12);

1 row created.

--
-- remove the A-E to give just a range
--
SQL>
SQL> with t1 as
  2  (
  3    select t.*, replace(expression,'(A-E)') expr2
  4    from  t
  5  )
  6  select * from t1;

      SCID       STNU NA    EXPRESSION RM           GRL EXPR2
---------- ---------- ----- ---------- ---------- ----- ----------
        87        100 AAA   3-5(A-E)   E003          12 3-5
        87        100 AAA   9(A-E)     Patti12       12 9
        87        100 AAA   6(A-E)     E207B         12 6
        87        100 AAA   8(A-E)     E002B         12 8
        87        100 AAA   9(A-E)     PattiAll      12 9
        87        100 AAA   7(A-E)     ECaf          12 7
        87        100 AAA   2(A-E)     EGym          12 2
        87        100 AAA   1(A-E)     E002B         12 1
        87        101 BBB   9(A-E)     MularzAll     12 9
        87        101 BBB   1(A-E)     E201          12 1
        87        101 BBB   7-8(A-E)                 12 7-8
        87        101 BBB   2(A-E)     EGym          12 2
        87        101 BBB   6(A-E)     ECaf          12 6
        87        101 BBB   3-5(A-E)   E003          12 3-5
        87        102 CCC   8(A-E)                   12 8
        87        102 CCC   2(A-E)     E200          12 2
        87        102 CCC   6(A-E)     E209          12 6
        87        102 CCC   1(A-E)     EGym          12 1
        87        102 CCC   7(A-E)     ECaf          12 7
        87        102 CCC   9(A-E)                   12 9
        87        102 CCC   3-5(A-E)   E008          12 3-5
        87        103 DDD   3-5(A-E)   E106B         12 3-5
        87        103 DDD   2(A-E)     E207A         12 2
        87        103 DDD   8(A-E)                   12 8
        87        103 DDD   6(A-E)     E209          12 6
        87        103 DDD   1(A-E)     EGym          12 1
        87        103 DDD   7(A-E)     ECaf          12 7
        87        103 DDD   9(A-E)                   12 9
        87        104 EEE   1(A-E)     EGym          12 1
        87        104 EEE   8(A-E)     E208A         12 8
        87        104 EEE   6(A-E)     E207B         12 6
        87        104 EEE   7(A-E)     E306          12 7
        87        104 EEE   2(A-E)     E208A         12 2
        87        104 EEE   5(A-E)     ECaf          12 5
        87        104 EEE   3(A-E)     E110          12 3
        87        104 EEE   4(A-E)     E001B         12 4

36 rows selected.

--
-- now extract start/end points from that range
--
SQL>
SQL>
SQL> with t1 as
  2  (
  3    select t.*, replace(expression,'(A-E)') expr2
  4    from  t
  5  ),
  6  t2 as
  7  (
  8    select t1.*,
  9      to_number(case when instr(expr2,'-') = 0 then expr2 else substr(expr2,1,instr(expr2,'-')-1) end) per_from,
 10      to_number(case when instr(expr2,'-') = 0 then expr2 else substr(expr2,instr(expr2,'-')+1) end) per_to
 11    from t1
 12  )
 13  select * from t2;

      SCID       STNU NA    EXPRESSION RM           GRL EXPR2        PER_FROM     PER_TO
---------- ---------- ----- ---------- ---------- ----- ---------- ---------- ----------
        87        100 AAA   3-5(A-E)   E003          12 3-5                 3          5
        87        100 AAA   9(A-E)     Patti12       12 9                   9          9
        87        100 AAA   6(A-E)     E207B         12 6                   6          6
        87        100 AAA   8(A-E)     E002B         12 8                   8          8
        87        100 AAA   9(A-E)     PattiAll      12 9                   9          9
        87        100 AAA   7(A-E)     ECaf          12 7                   7          7
        87        100 AAA   2(A-E)     EGym          12 2                   2          2
        87        100 AAA   1(A-E)     E002B         12 1                   1          1
        87        101 BBB   9(A-E)     MularzAll     12 9                   9          9
        87        101 BBB   1(A-E)     E201          12 1                   1          1
        87        101 BBB   7-8(A-E)                 12 7-8                 7          8
        87        101 BBB   2(A-E)     EGym          12 2                   2          2
        87        101 BBB   6(A-E)     ECaf          12 6                   6          6
        87        101 BBB   3-5(A-E)   E003          12 3-5                 3          5
        87        102 CCC   8(A-E)                   12 8                   8          8
        87        102 CCC   2(A-E)     E200          12 2                   2          2
        87        102 CCC   6(A-E)     E209          12 6                   6          6
        87        102 CCC   1(A-E)     EGym          12 1                   1          1
        87        102 CCC   7(A-E)     ECaf          12 7                   7          7
        87        102 CCC   9(A-E)                   12 9                   9          9
        87        102 CCC   3-5(A-E)   E008          12 3-5                 3          5
        87        103 DDD   3-5(A-E)   E106B         12 3-5                 3          5
        87        103 DDD   2(A-E)     E207A         12 2                   2          2
        87        103 DDD   8(A-E)                   12 8                   8          8
        87        103 DDD   6(A-E)     E209          12 6                   6          6
        87        103 DDD   1(A-E)     EGym          12 1                   1          1
        87        103 DDD   7(A-E)     ECaf          12 7                   7          7
        87        103 DDD   9(A-E)                   12 9                   9          9
        87        104 EEE   1(A-E)     EGym          12 1                   1          1
        87        104 EEE   8(A-E)     E208A         12 8                   8          8
        87        104 EEE   6(A-E)     E207B         12 6                   6          6
        87        104 EEE   7(A-E)     E306          12 7                   7          7
        87        104 EEE   2(A-E)     E208A         12 2                   2          2
        87        104 EEE   5(A-E)     ECaf          12 5                   5          5
        87        104 EEE   3(A-E)     E110          12 3                   3          3
        87        104 EEE   4(A-E)     E001B         12 4                   4          4

36 rows selected.

--
-- use those start/end points to create multiple rows, one for each period in the start/end range
--
SQL>
SQL>
SQL> with t1 as
  2  (
  3    select t.*, replace(expression,'(A-E)') expr2
  4    from  t
  5  ),
  6  t2 as
  7  (
  8    select t1.*,
  9      to_number(case when instr(expr2,'-') = 0 then expr2 else substr(expr2,1,instr(expr2,'-')-1) end) per_from,
 10      to_number(case when instr(expr2,'-') = 0 then expr2 else substr(expr2,instr(expr2,'-')+1) end) per_to
 11    from t1
 12  ),
 13  t3 as
 14  (
 15    select t2.*, per_range
 16    from t2,
 17      lateral(
 18        select rownum-1+per_from per_range
 19        from dual
 20        connect by level <= per_to - per_from + 1
 21      )
 22  )
 23  select * from t3;

      SCID       STNU NA    EXPRESSION RM           GRL EXPR2        PER_FROM     PER_TO  PER_RANGE
---------- ---------- ----- ---------- ---------- ----- ---------- ---------- ---------- ----------
        87        100 AAA   3-5(A-E)   E003          12 3-5                 3          5          3
        87        100 AAA   3-5(A-E)   E003          12 3-5                 3          5          4
        87        100 AAA   3-5(A-E)   E003          12 3-5                 3          5          5
        87        100 AAA   9(A-E)     Patti12       12 9                   9          9          9
        87        100 AAA   6(A-E)     E207B         12 6                   6          6          6
        87        100 AAA   8(A-E)     E002B         12 8                   8          8          8
        87        100 AAA   9(A-E)     PattiAll      12 9                   9          9          9
        87        100 AAA   7(A-E)     ECaf          12 7                   7          7          7
        87        100 AAA   2(A-E)     EGym          12 2                   2          2          2
        87        100 AAA   1(A-E)     E002B         12 1                   1          1          1
        87        101 BBB   9(A-E)     MularzAll     12 9                   9          9          9
        87        101 BBB   1(A-E)     E201          12 1                   1          1          1
        87        101 BBB   7-8(A-E)                 12 7-8                 7          8          7
        87        101 BBB   7-8(A-E)                 12 7-8                 7          8          8
        87        101 BBB   2(A-E)     EGym          12 2                   2          2          2
        87        101 BBB   6(A-E)     ECaf          12 6                   6          6          6
        87        101 BBB   3-5(A-E)   E003          12 3-5                 3          5          3
        87        101 BBB   3-5(A-E)   E003          12 3-5                 3          5          4
        87        101 BBB   3-5(A-E)   E003          12 3-5                 3          5          5
        87        102 CCC   8(A-E)                   12 8                   8          8          8
        87        102 CCC   2(A-E)     E200          12 2                   2          2          2
        87        102 CCC   6(A-E)     E209          12 6                   6          6          6
        87        102 CCC   1(A-E)     EGym          12 1                   1          1          1
        87        102 CCC   7(A-E)     ECaf          12 7                   7          7          7
        87        102 CCC   9(A-E)                   12 9                   9          9          9
        87        102 CCC   3-5(A-E)   E008          12 3-5                 3          5          3
        87        102 CCC   3-5(A-E)   E008          12 3-5                 3          5          4
        87        102 CCC   3-5(A-E)   E008          12 3-5                 3          5          5
        87        103 DDD   3-5(A-E)   E106B         12 3-5                 3          5          3
        87        103 DDD   3-5(A-E)   E106B         12 3-5                 3          5          4
        87        103 DDD   3-5(A-E)   E106B         12 3-5                 3          5          5
        87        103 DDD   2(A-E)     E207A         12 2                   2          2          2
        87        103 DDD   8(A-E)                   12 8                   8          8          8
        87        103 DDD   6(A-E)     E209          12 6                   6          6          6
        87        103 DDD   1(A-E)     EGym          12 1                   1          1          1
        87        103 DDD   7(A-E)     ECaf          12 7                   7          7          7
        87        103 DDD   9(A-E)                   12 9                   9          9          9
        87        104 EEE   1(A-E)     EGym          12 1                   1          1          1
        87        104 EEE   8(A-E)     E208A         12 8                   8          8          8
        87        104 EEE   6(A-E)     E207B         12 6                   6          6          6
        87        104 EEE   7(A-E)     E306          12 7                   7          7          7
        87        104 EEE   2(A-E)     E208A         12 2                   2          2          2
        87        104 EEE   5(A-E)     ECaf          12 5                   5          5          5
        87        104 EEE   3(A-E)     E110          12 3                   3          3          3
        87        104 EEE   4(A-E)     E001B         12 4                   4          4          4

45 rows selected.

--
-- then pivot the rows into the 12 periods
--
SQL>
SQL>
SQL>
SQL> with t1 as
  2  (
  3    select t.*, replace(expression,'(A-E)') expr2
  4    from  t
  5  ),
  6  t2 as
  7  (
  8    select t1.*,
  9      to_number(case when instr(expr2,'-') = 0 then expr2 else substr(expr2,1,instr(expr2,'-')-1) end) per_from,
 10      to_number(case when instr(expr2,'-') = 0 then expr2 else substr(expr2,instr(expr2,'-')+1) end) per_to
 11    from t1
 12  ),
 13  t3 as
 14  (
 15    select t2.*, per_range
 16    from t2,
 17      lateral(
 18        select rownum-1+per_from per_range
 19        from dual
 20        connect by level <= per_to - per_from + 1
 21      )
 22  )
 23  select *
 24  from   (select stnu, na, grl, rm, per_range
 25          from   t3)
 26  pivot  (max(rm) as rm1 for (per_range)
 27    in (1 as p1, 2 as p2, 3 as p3, 4 as p4, 5 as p5, 6 as p6,
 28        7 as p7, 8 as p8, 9 as p9, 10 as p10, 11 as p11, 12 as p12
 29        )
 30    )
 31  order by 1,2,3;

      STNU NA      GRL P1_RM1     P2_RM1     P3_RM1     P4_RM1     P5_RM1     P6_RM1     P7_RM1     P8_RM1     P9_RM1     P10_RM1    P11_RM1    P12_RM1
---------- ----- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       100 AAA      12 E002B      EGym       E003       E003       E003       E207B      ECaf       E002B      PattiAll
       101 BBB      12 E201       EGym       E003       E003       E003       ECaf                             MularzAll
       102 CCC      12 EGym       E200       E008       E008       E008       E209       ECaf
       103 DDD      12 EGym       E207A      E106B      E106B      E106B      E209       ECaf
       104 EEE      12 EGym       E208A      E110       E001B      ECaf       E207B      E306       E208A

SQL>

Rating

  (3 ratings)

Comments

Thank you.

V Sagaram, November 08, 2022 - 2:05 pm UTC

Just what I was looking for!!

Thank you very much.
Connor McDonald
November 09, 2022 - 1:35 am UTC

glad we could help

Addition/Change

Venkat Sagaram, November 09, 2022 - 3:11 pm UTC

Hello-

Thank you for the solution provided; however I have thrown a wrench:-)

I have the Expression being sent as different data sets as follows (this is just an example) :
EXPRESSION
1(A-B,D-E)
1(A-E)
1(A-E) 2(C)
1(A-E) 2(E)
1(C) 2(A-E)
10(A,C,E)
10(B-D)
11(A-B)
11(A-E)
11-12(A-B)
11-12(A-E)
2(A-B,D-E)
2(A-D)
2(A-E)
3(A-E)
4(A-E)
5(A-C,E)
5(A-E)
5(A-E) 6(C)
5(D) 6(A-E)
6(A-B,D-E)
6(A-C,E)
6(A-E)
6(D) 7(A-E)
7(A-E)
7-8(A-E)
8(A-E)
9(A-E)
9-10(A-E)


Not sure how this can be accommodated.






Thank you figured out...

Venkat Sagaram, November 09, 2022 - 3:35 pm UTC

Please ignore above questions...

Figured out how to get the required data sets.
Connor McDonald
November 11, 2022 - 3:23 am UTC

nice work

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.