First task is to join all these tables together properly and to normalize the data. Then you can ask how to pivot the rows after you make sure the source data is correct.
The source data is complicated by two structural issues:
- Subjects in the
table_timetable table are listed across multiple columns. To normalise this data we can query the table 1 time for each of the subject columns and union the results together
- Students in the
table_timetable table are stored as a CSV string value, MariaDB doesn't have a built-in function to query this type of data, but we can use a CTE to split out the names into individual rows, this is explored in this SO question: SQL (Maria DB) split string separated by comma to rows
For everyone at home, if you have a similar requirement without these two complications, then this simplified fiddle might help: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=9dfce7084321699d1262bb50ca724d9e
The following query will normalize the recordset:
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
For my test DB this comes out to:
|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|
...
See the fiddle here: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f31cab287fdd575054e3af457a9cf14d
Finally to PIVOT this data into columns for each student... this is where MariaDB really lets us down, there is no in-built support for PIVOT, you have to manually construct the result yourself. You could use more CTEs but if your application is able to construct the SQL, a simpler query is to simply use a CASE statement to construct each column:
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`;
NOTE: I commented out the join on table_student because you do not need any information from that table, in fact it would be hard to include it in this output, you are best to leave that to your application logic if you need is to resolve the column names.
You might use the join to add filter criteria, so I leave it there for you to explore.
This results in:
| 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 |
|
2021-08-18 |
This is a simpler form of PIVOT to maintain if you are generating the query in your code, notice that you just need to repeat the max statement for each student column.
This final solution is in the following db<>fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f07c943d3d92ea0de8d91021728ab7c0
For alternate methods see Pivoting in MariaDB
