MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.3.31-MariaDB


CREATE Table table_toteach (
	`teach-id` char(10),
	`teach` varchar(100),
	`teacher` varchar(100)
);
INSERT INTO table_toteach (`teach-id`,`teach`, `teacher`) VALUES
('1.0','Subj1-','Tname1'),
('1.2','Subj2-','Tname2'),
('1.5','Subj3-','Tname3'),
('2.0','Subj4-','Tname4');
CREATE TABLE table_students (
	student varchar(10)
);
INSERT INTO table_students (student) VALUES
('stname1'),
('stname2'),
('stname3'),
('stname5'),
('stname4');
CREATE TABLE table_stpresents (
	student varchar(10),
	`Date` date,
	present varchar(10)
);
INSERT INTO table_stpresents (student,`Date`,present) VALUES
('stname5','2021-08-16','- yes -'),
('stname4','2021-08-16','- yes -'),
('stname3','2021-08-16','- yes -'),
('stname2','2021-08-16','- yes -'),
('stname1','2021-08-16','- yes -'),
('stname1','2021-08-17','- no -'),
('stname2','2021-08-17','- yes -'),
('stname1','2021-08-18','- yes -'),
('stname2','2021-08-18','- yes -'),
('stname5','2021-08-18','- yes -'),
('stname3','2021-08-19','- yes -'),
('stname4','2021-08-19','- yes -'),
('stname5','2021-08-19','- no -');
CREATE TABLE table_timetable (
	`Date` date,
	subj1 varchar(50),
	subj2 varchar(50),
	subj3 varchar(50),
	stnames varchar(150)
);
INSERT INTO table_timetable (`Date`,subj1,subj2,subj3,stnames) VALUES
('2021-08-16','Subj1-','Subj2-','Subj3-','stname1, stname2, stname3'),
('2021-08-16','Subj1-','Subj3-','Subj2-','stname1, stname4, stname2'),
('2021-08-16','Subj1-','Subj2-','Subj3-','stname2, stname3, stname5'),
('2021-08-17','Subj2-','Subj1-','Subj3-','stname1, stname3, stname5'),
('2021-08-17','Subj2-','Subj1-','Subj1-','stname2, stname1, stname4'),
('2021-08-17','Subj1-','Subj4-','Subj2-','stname2, stname3, stname5'),
('2021-08-18','Subj1-','Subj3-','Subj2-','stname1, stname2, stname3'),
('2021-08-18','Subj4-','Subj2-','Subj1-','stname2, stname3, stname5'),
('2021-08-18','Subj3-','Subj1-','Subj4-','stname1, stname4, stname5'),
('2021-08-19','Subj1-','Subj3-','Subj2-','stname1, stname4, stname2'),
('2021-08-19','Subj2-','Subj1-','Subj4-','stname1, stname3, stname5');
SELECT 'Use CTE to perform STRING_SPLIT in the CSV column' AS Example;

with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )

select `Date`,subj1,subj2,subj3,stname
from cte
where lev > 1


Example


Use CTE to perform STRING_SPLIT in the CSV column




Date
subj1
subj2
subj3
stname


2021-08-16
Subj1-
Subj2-
Subj3-
stname1

2021-08-16
Subj1-
Subj3-
Subj2-
stname1

2021-08-16
Subj1-
Subj2-
Subj3-
stname2

2021-08-17
Subj2-
Subj1-
Subj3-
stname1

2021-08-17
Subj2-
Subj1-
Subj1-
stname2

2021-08-17
Subj1-
Subj4-
Subj2-
stname2

2021-08-18
Subj1-
Subj3-
Subj2-
stname1

2021-08-18
Subj4-
Subj2-
Subj1-
stname2

2021-08-18
Subj3-
Subj1-
Subj4-
stname1

2021-08-19
Subj1-
Subj3-
Subj2-
stname1

2021-08-19
Subj2-
Subj1-
Subj4-
stname1

2021-08-16
Subj1-
Subj2-
Subj3-
stname2

2021-08-16
Subj1-
Subj3-
Subj2-
stname4

2021-08-16
Subj1-
Subj2-
Subj3-
stname3

2021-08-17
Subj2-
Subj1-
Subj3-
stname3

2021-08-17
Subj2-
Subj1-
Subj1-
stname1

2021-08-17
Subj1-
Subj4-
Subj2-
stname3

2021-08-18
Subj1-
Subj3-
Subj2-
stname2

2021-08-18
Subj4-
Subj2-
Subj1-
stname3

2021-08-18
Subj3-
Subj1-
Subj4-
stname4

2021-08-19
Subj1-
Subj3-
Subj2-
stname4

2021-08-19
Subj2-
Subj1-
Subj4-
stname3

2021-08-16
Subj1-
Subj2-
Subj3-
stname3

2021-08-16
Subj1-
Subj3-
Subj2-
stname2

2021-08-16
Subj1-
Subj2-
Subj3-
stname5

2021-08-17
Subj2-
Subj1-
Subj3-
stname5

2021-08-17
Subj2-
Subj1-
Subj1-
stname4

2021-08-17
Subj1-
Subj4-
Subj2-
stname5

2021-08-18
Subj1-
Subj3-
Subj2-
stname3

2021-08-18
Subj4-
Subj2-
Subj1-
stname5

2021-08-18
Subj3-
Subj1-
Subj4-
stname5

2021-08-19
Subj1-
Subj3-
Subj2-
stname2

2021-08-19
Subj2-
Subj1-
Subj4-
stname5


SELECT 'Normalize Subject columns - with STRING_SPLIT' AS Example;

with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )
, bySubject as (
select `Date`,subj1 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj2 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj3 as subj, stname
from cte
where lev > 1
)
select `Date`,subj,stname
from bySubject


Example


Normalize Subject columns - with STRING_SPLIT




Date
subj
stname


2021-08-16
Subj1-
stname1

2021-08-16
Subj1-
stname1

2021-08-16
Subj1-
stname2

2021-08-17
Subj2-
stname1

2021-08-17
Subj2-
stname2

2021-08-17
Subj1-
stname2

2021-08-18
Subj1-
stname1

2021-08-18
Subj4-
stname2

2021-08-18
Subj3-
stname1

2021-08-19
Subj1-
stname1

2021-08-19
Subj2-
stname1

2021-08-16
Subj1-
stname2

2021-08-16
Subj1-
stname4

2021-08-16
Subj1-
stname3

2021-08-17
Subj2-
stname3

2021-08-17
Subj2-
stname1

2021-08-17
Subj1-
stname3

2021-08-18
Subj1-
stname2

2021-08-18
Subj4-
stname3

2021-08-18
Subj3-
stname4

2021-08-19
Subj1-
stname4

2021-08-19
Subj2-
stname3

2021-08-16
Subj1-
stname3

2021-08-16
Subj1-
stname2

2021-08-16
Subj1-
stname5

2021-08-17
Subj2-
stname5

2021-08-17
Subj2-
stname4

2021-08-17
Subj1-
stname5

2021-08-18
Subj1-
stname3

2021-08-18
Subj4-
stname5

2021-08-18
Subj3-
stname5

2021-08-19
Subj1-
stname2

2021-08-19
Subj2-
stname5

2021-08-16
Subj2-
stname1

2021-08-16
Subj3-
stname1

2021-08-16
Subj2-
stname2

2021-08-17
Subj1-
stname1

2021-08-17
Subj1-
stname2

2021-08-17
Subj4-
stname2

2021-08-18
Subj3-
stname1

2021-08-18
Subj2-
stname2

2021-08-18
Subj1-
stname1

2021-08-19
Subj3-
stname1

2021-08-19
Subj1-
stname1

2021-08-16
Subj2-
stname2

2021-08-16
Subj3-
stname4

2021-08-16
Subj2-
stname3

2021-08-17
Subj1-
stname3

2021-08-17
Subj1-
stname1

2021-08-17
Subj4-
stname3

2021-08-18
Subj3-
stname2

2021-08-18
Subj2-
stname3

2021-08-18
Subj1-
stname4

2021-08-19
Subj3-
stname4

2021-08-19
Subj1-
stname3

2021-08-16
Subj2-
stname3

2021-08-16
Subj3-
stname2

2021-08-16
Subj2-
stname5

2021-08-17
Subj1-
stname5

2021-08-17
Subj1-
stname4

2021-08-17
Subj4-
stname5

2021-08-18
Subj3-
stname3

2021-08-18
Subj2-
stname5

2021-08-18
Subj1-
stname5

2021-08-19
Subj3-
stname2

2021-08-19
Subj1-
stname5

2021-08-16
Subj3-
stname1

2021-08-16
Subj2-
stname1

2021-08-16
Subj3-
stname2

2021-08-17
Subj3-
stname1

2021-08-17
Subj1-
stname2

2021-08-17
Subj2-
stname2

2021-08-18
Subj2-
stname1

2021-08-18
Subj1-
stname2

2021-08-18
Subj4-
stname1

2021-08-19
Subj2-
stname1

2021-08-19
Subj4-
stname1

2021-08-16
Subj3-
stname2

2021-08-16
Subj2-
stname4

2021-08-16
Subj3-
stname3

2021-08-17
Subj3-
stname3

2021-08-17
Subj1-
stname1

2021-08-17
Subj2-
stname3

2021-08-18
Subj2-
stname2

2021-08-18
Subj1-
stname3

2021-08-18
Subj4-
stname4

2021-08-19
Subj2-
stname4

2021-08-19
Subj4-
stname3

2021-08-16
Subj3-
stname3

2021-08-16
Subj2-
stname2

2021-08-16
Subj3-
stname5

2021-08-17
Subj3-
stname5

2021-08-17
Subj1-
stname4

2021-08-17
Subj2-
stname5

2021-08-18
Subj2-
stname3

2021-08-18
Subj1-
stname5

2021-08-18
Subj4-
stname5

2021-08-19
Subj2-
stname2

2021-08-19
Subj4-
stname5


SELECT 'Group Normalized Set, to verify the expected PIVOT aggregate expression' AS Example
UNION ALL SELECT 'Showing Attendance for each day' AS Example
UNION ALL SELECT 'As well as an indication if there is no matching attendence record at all';

with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )
, bySubject as (
select `Date`,subj1 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj2 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj3 as subj, stname
from cte
where lev > 1
)
select tt.`Date`,subj,stname,present
from bySubject tt
LEFT OUTER JOIN table_stpresents attendance ON tt.Date = attendance.Date AND tt.stname = attendance.student
ORDER BY `Date`,subj,stname


Example


Group Normalized Set, to verify the expected PIVOT aggregate expression

Showing Attendance for each day

As well as an indication if there is no matching attendence record at all




Date
subj
stname
present


2021-08-16
Subj1-
stname1
- yes -

2021-08-16
Subj1-
stname1
- yes -

2021-08-16
Subj1-
stname2
- yes -

2021-08-16
Subj1-
stname2
- yes -

2021-08-16
Subj1-
stname2
- yes -

2021-08-16
Subj1-
stname3
- yes -

2021-08-16
Subj1-
stname3
- yes -

2021-08-16
Subj1-
stname4
- yes -

2021-08-16
Subj1-
stname5
- yes -

2021-08-16
Subj2-
stname1
- yes -

2021-08-16
Subj2-
stname1
- yes -

2021-08-16
Subj2-
stname2
- yes -

2021-08-16
Subj2-
stname2
- yes -

2021-08-16
Subj2-
stname2
- yes -

2021-08-16
Subj2-
stname3
- yes -

2021-08-16
Subj2-
stname3
- yes -

2021-08-16
Subj2-
stname4
- yes -

2021-08-16
Subj2-
stname5
- yes -

2021-08-16
Subj3-
stname1
- yes -

2021-08-16
Subj3-
stname1
- yes -

2021-08-16
Subj3-
stname2
- yes -

2021-08-16
Subj3-
stname2
- yes -

2021-08-16
Subj3-
stname2
- yes -

2021-08-16
Subj3-
stname3
- yes -

2021-08-16
Subj3-
stname3
- yes -

2021-08-16
Subj3-
stname4
- yes -

2021-08-16
Subj3-
stname5
- yes -

2021-08-17
Subj1-
stname1
- no -

2021-08-17
Subj1-
stname1
- no -

2021-08-17
Subj1-
stname1
- no -

2021-08-17
Subj1-
stname2
- yes -

2021-08-17
Subj1-
stname2
- yes -

2021-08-17
Subj1-
stname2
- yes -

2021-08-17
Subj1-
stname3
null

2021-08-17
Subj1-
stname3
null

2021-08-17
Subj1-
stname4
null

2021-08-17
Subj1-
stname4
null

2021-08-17
Subj1-
stname5
null

2021-08-17
Subj1-
stname5
null

2021-08-17
Subj2-
stname1
- no -

2021-08-17
Subj2-
stname1
- no -

2021-08-17
Subj2-
stname2
- yes -

2021-08-17
Subj2-
stname2
- yes -

2021-08-17
Subj2-
stname3
null

2021-08-17
Subj2-
stname3
null

2021-08-17
Subj2-
stname4
null

2021-08-17
Subj2-
stname5
null

2021-08-17
Subj2-
stname5
null

2021-08-17
Subj3-
stname1
- no -

2021-08-17
Subj3-
stname3
null

2021-08-17
Subj3-
stname5
null

2021-08-17
Subj4-
stname2
- yes -

2021-08-17
Subj4-
stname3
null

2021-08-17
Subj4-
stname5
null

2021-08-18
Subj1-
stname1
- yes -

2021-08-18
Subj1-
stname1
- yes -

2021-08-18
Subj1-
stname2
- yes -

2021-08-18
Subj1-
stname2
- yes -

2021-08-18
Subj1-
stname3
null

2021-08-18
Subj1-
stname3
null

2021-08-18
Subj1-
stname4
null

2021-08-18
Subj1-
stname5
- yes -

2021-08-18
Subj1-
stname5
- yes -

2021-08-18
Subj2-
stname1
- yes -

2021-08-18
Subj2-
stname2
- yes -

2021-08-18
Subj2-
stname2
- yes -

2021-08-18
Subj2-
stname3
null

2021-08-18
Subj2-
stname3
null

2021-08-18
Subj2-
stname5
- yes -

2021-08-18
Subj3-
stname1
- yes -

2021-08-18
Subj3-
stname1
- yes -

2021-08-18
Subj3-
stname2
- yes -

2021-08-18
Subj3-
stname3
null

2021-08-18
Subj3-
stname4
null

2021-08-18
Subj3-
stname5
- yes -

2021-08-18
Subj4-
stname1
- yes -

2021-08-18
Subj4-
stname2
- yes -

2021-08-18
Subj4-
stname3
null

2021-08-18
Subj4-
stname4
null

2021-08-18
Subj4-
stname5
- yes -

2021-08-18
Subj4-
stname5
- yes -

2021-08-19
Subj1-
stname1
null

2021-08-19
Subj1-
stname1
null

2021-08-19
Subj1-
stname2
null

2021-08-19
Subj1-
stname3
- yes -

2021-08-19
Subj1-
stname4
- yes -

2021-08-19
Subj1-
stname5
- no -

2021-08-19
Subj2-
stname1
null

2021-08-19
Subj2-
stname1
null

2021-08-19
Subj2-
stname2
null

2021-08-19
Subj2-
stname3
- yes -

2021-08-19
Subj2-
stname4
- yes -

2021-08-19
Subj2-
stname5
- no -

2021-08-19
Subj3-
stname1
null

2021-08-19
Subj3-
stname2
null

2021-08-19
Subj3-
stname4
- yes -

2021-08-19
Subj4-
stname1
null

2021-08-19
Subj4-
stname3
- yes -

2021-08-19
Subj4-
stname5
- no -


with recursive cte as (
      select `Date`,subj1,subj2,subj3,'            ' as stname, concat(stnames, ',') as stnames, 1 as lev
      from table_timetable
      union all
      select `Date`,subj1,subj2,subj3,substring_index(stnames, ',', 1),
             substr(stnames, instr(stnames, ',') + 2), lev + 1
      from cte
      where stnames like '%,%'
     )
, bySubject as (
select `Date`,subj1 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj2 as subj, stname
from cte
where lev > 1
union all
select `Date`,subj3 as subj, stname
from cte
where lev > 1
)
select teacher.`teach-id`, teach, teacher
    , MAX(CASE WHEN stname = 'stname1' THEN attendance.Date END) AS stname1
    , MAX(CASE WHEN stname = 'stname2' THEN attendance.Date END) AS stname2
    , MAX(CASE WHEN stname = 'stname3' THEN attendance.Date END) AS stname3
    , MAX(CASE WHEN stname = 'stname4' THEN attendance.Date END) AS stname4
    , MAX(CASE WHEN stname = 'stname5' THEN attendance.Date END) AS stname5
FROM bySubject tt
/*INNER JOIN table_students s ON tt.stname = s.student*/
INNER JOIN table_stpresents attendance ON tt.Date = attendance.Date AND tt.stname = attendance.student
INNER JOIN table_toteach teacher ON tt.subj = teacher.teach
WHERE attendance.present = '- yes -'
GROUP BY teacher.`teach-id`, teach, teacher
ORDER BY `teach-id`;


teach-id
teach
teacher
stname1
stname2
stname3
stname4
stname5


1.0
Subj1-
Tname1
2021-08-18
2021-08-18
2021-08-19
2021-08-19
2021-08-18

1.2
Subj2-
Tname2
2021-08-18
2021-08-18
2021-08-19
2021-08-19
2021-08-18

1.5
Subj3-
Tname3
2021-08-18
2021-08-18
2021-08-16
2021-08-19
2021-08-18

2.0
Subj4-
Tname4
2021-08-18
2021-08-18
2021-08-19
null
2021-08-18

Date	subj1	subj2	subj3	stname
2021-08-16	Subj1-	Subj2-	Subj3-	stname1
2021-08-16	Subj1-	Subj3-	Subj2-	stname1
2021-08-16	Subj1-	Subj2-	Subj3-	stname2
2021-08-17	Subj2-	Subj1-	Subj3-	stname1
2021-08-17	Subj2-	Subj1-	Subj1-	stname2
2021-08-17	Subj1-	Subj4-	Subj2-	stname2
2021-08-18	Subj1-	Subj3-	Subj2-	stname1
2021-08-18	Subj4-	Subj2-	Subj1-	stname2
2021-08-18	Subj3-	Subj1-	Subj4-	stname1
2021-08-19	Subj1-	Subj3-	Subj2-	stname1
2021-08-19	Subj2-	Subj1-	Subj4-	stname1
2021-08-16	Subj1-	Subj2-	Subj3-	stname2
2021-08-16	Subj1-	Subj3-	Subj2-	stname4
2021-08-16	Subj1-	Subj2-	Subj3-	stname3
2021-08-17	Subj2-	Subj1-	Subj3-	stname3
2021-08-17	Subj2-	Subj1-	Subj1-	stname1
2021-08-17	Subj1-	Subj4-	Subj2-	stname3
2021-08-18	Subj1-	Subj3-	Subj2-	stname2
2021-08-18	Subj4-	Subj2-	Subj1-	stname3
2021-08-18	Subj3-	Subj1-	Subj4-	stname4
2021-08-19	Subj1-	Subj3-	Subj2-	stname4
2021-08-19	Subj2-	Subj1-	Subj4-	stname3
2021-08-16	Subj1-	Subj2-	Subj3-	stname3
2021-08-16	Subj1-	Subj3-	Subj2-	stname2
2021-08-16	Subj1-	Subj2-	Subj3-	stname5
2021-08-17	Subj2-	Subj1-	Subj3-	stname5
2021-08-17	Subj2-	Subj1-	Subj1-	stname4
2021-08-17	Subj1-	Subj4-	Subj2-	stname5
2021-08-18	Subj1-	Subj3-	Subj2-	stname3
2021-08-18	Subj4-	Subj2-	Subj1-	stname5
2021-08-18	Subj3-	Subj1-	Subj4-	stname5
2021-08-19	Subj1-	Subj3-	Subj2-	stname2
2021-08-19	Subj2-	Subj1-	Subj4-	stname5

teach-id	teach	teacher	stname1	stname2	stname3	stname4	stname5
1.0	Subj1-	Tname1	2021-08-18	2021-08-18	2021-08-19	2021-08-19	2021-08-18
1.2	Subj2-	Tname2	2021-08-18	2021-08-18	2021-08-19	2021-08-19	2021-08-18
1.5	Subj3-	Tname3	2021-08-18	2021-08-18	2021-08-16	2021-08-19	2021-08-18
2.0	Subj4-	Tname4	2021-08-18	2021-08-18	2021-08-19	null	2021-08-18