Need to PIVOT 1 table in 4 JOINS

Question

So, this is a verry specific Problem i hope u guys understand what i want to do there:

(MariaDB)

my tables:

table_toteach:

teach-id	teach	teacher	....	....
1.0	Subj1-	Tname1
1.2	Subj2-	Tname1

table_students:

student	...	...
stname1	--	--
stname2	--	--
stname3	--	--
stname4	--	--
(x-amount of stname)	--	--

table_stpresents:

This is an attendance record and will have 1 row for each student and each day. If the student is present at the start of the day, then they are expected to have attended each of their scheduled classes for that day.

student	Date	present
stname1	Date1	- yes -
stname2	Date1	- no -
x amount of stname	Date1	-yes-
stname1	Date2	-no-
stname2	Date2	-yes-
x amount of stname	Date2	-yes-

table_timetable:

Student names are referenced here as a comma delimited string (CSV) value, there is 1 row for each timetabled class in each day

date	subj1	subj2	subj3	stnames
Date1	Subj1-	Subj3-	Subj2-	stname1, stname2, stname5
DateX	Subj3-	Subj2-	Subj1-	stname1, stname4, stname3, stname5

The expected Result:

Show 1 row for each subject that has one column for each student and that contains the LDUS, the Latest Date when the subject has been taught to this student, based on the days when the student was present and the timetable records that correspond to that student.

The table columns-count change by choosing a different subject group (this table will be part of a custom UI)

Note: my code is able to dynamically generate the sql code, so there is no need for a dynamic SQL solution.

teach-id	teach	teacher	stname1	stname2	.....	stnameX
1.0	Subj1-	Tname1	-LDUS-	-LDUS-	-LDUS-
1.2	Subj2-	Tname2	-LDUS-	-LDUS-	-LDUS-

Update

I have prepared a fiddle with some example data: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=4bf2542240eaa86e4f4624c1e376215a

Answer 1

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:

1. 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
2. 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