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