Advanced table pivoting in MySQL and Dynamic Jasper

Question

I have a MySQL Table that stores my classes in a timetable manner. the columns are fixed and the rows are id, day,class, classid, 1,2,3,4,5,6,7,8 This is my table structure

_______________________________________________________________________________
| id | day | class | class_id |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
|____|_____|_______|__________|_____|_____|_____|_____|_____|_____|_____|_____|
| 1  | Mon | FIA   |    1     | Eng | Che | Mat | Geo | Cre | Kis | His | Bio |
| 1  | Tue | FIA   |    1     | Geo | Cre | His | Che | His | Kis | Bio | Mat |
| 1  | Wed | FIA   |    1     | Mat | Eng | Geo | Geo | Cre | Bio | Cre | Bio |
| 1  | Thu | FIA   |    1     | Eng | Che | Mat | Eng | His | Kis | His | Geo |
| 1  | Fri | FIA   |    1     | Geo | Bio | Eng | Geo | Che | Mat | His | Bio |
| 1  | Mon | FIB   |    1     | Cre | Che | Mat | Eng | Cre | Kis | Eng | Che |
| 1  | Tue | FIB   |    1     | Eng | Che | Bio | Geo | Bio | Mat | His | Eng |
| 1  | Wed | FIB   |    1     | Eng | Eng | Mat | Mat | Cre | Eng | Geo | Bio |
| 1  | Thu | FIB   |    1     | Cre | Cre | Mat | Geo | Eng | Kis | Mat | Eng |
| 1  | Fri | FIB   |    1     | Mat | Che | Eng | Eng | Mat | Che | Mat | Bio |
| 1  | Mon | FIC   |    1     | Eng | Che | Che | Geo | Cre | Kis | His | Mat |
| 1  | Tue | FIC   |    1     | Che | Eng | Mat | His | Mat | Che | Che | Bio |
| 1  | Wed | FIC   |    1     | Cre | Che | His | Che | Bio | Kis | Bio | Mat |
| 1  | Thu | FIC   |    1     | Eng | Mat | Mat | Geo | Cre | Kis | His | Mat |
| 1  | Fri | FIC   |    1     | Eng | Che | His | Geo | Cre | Kis | His | Bio |
|____|_____|_______|__________|_____|_____|_____|_____|_____|_____|_____|_____|

What I want to do is to create a summary timetable for all the classes. I am creating this with dynamic jasper but the most important thing is that I get the query right. This is what I am looking forward to achieving.

_____________________________________________________________________________________________________________________________________________________________________
|    |              Mon              |              Tue              |              Wed              |              Thu              |              Fri              |
|    | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
_____________________________________________________________________________________________________________________________________________________________________
|F1A |Eng|Che|Mat|Geo|Cre|Kis|His|Bio|Geo|Cre|His|Che|His|Kis|Bio|Mat|Mat|Eng|Geo|Geo|Cre|Bio|Cre|Bio|Eng|Che|Mat|Eng|His|Kis|His|Geo|Geo|Bio|Eng|Geo|Che|Mat|His|Bio|
|F1B |Cre|Che|Mat|Eng|Cre|Kis|Eng|Che|Eng|Che|Bio|Geo|Bio|Mat|His|Eng|Eng|Eng|Mat|Mat|Cre|Eng|Geo|Bio|Cre|Cre|Mat|Geo|Eng|Kis|Mat|Eng|Mat|Che|Eng|Eng|Mat|Che|Mat|Bio|
|F1C |Eng|Che|Che|Geo|Cre|Kis|His|Mat|Che|Eng|Mat|His|Mat|Che|Che|Bio|Cre|Che|His|Che|Bio|Kis|Bio|Mat|Eng|Mat|Mat|Geo|Cre|Kis|His|Mat|Eng|Che|His|Geo|Cre|Kis|His|Bio|
_____________________________________________________________________________________________________________________________________________________________________

You can ignore the top most column with days, I just need a query that will give me the columns 122345678 for each day from Monday to Friday.

This looks more like a spreadsheet than a table. Why not normalise your schema? — Strawberry, Oct 29 '20 at 13:17
@Strawberry this is a continuation of someone else work, changing the table structure will put me in a mess with the amount of work I will have to do for the entire project. — Benson Kiprono, Oct 29 '20 at 13:22
Join 5 table copies by `class`, filter each copy for definite day, select needed data. — Akina, Oct 29 '20 at 13:26
I suspect it would be worth it! Anyway, I can't advise further as this is a problem for application code, and I don't know jasper — Strawberry, Oct 29 '20 at 13:27

score 1 · Accepted Answer · answered Oct 29 '20 at 15:40

This can be done in SQL, although this is probably not the best tool for the job, as others commented already.

The logic is to use conditional aggregation... and a lot of typing. Here is an example for 3 columns (col1, col2 and col3) and two days ('Mon' and 'Tue'):

select class,
    max(case when day = 'Mon' then col1 end) mon_1,
    max(case when day = 'Mon' then col2 end) mon_2,
    max(case when day = 'Mon' then col3 end) mon_3,
    max(case when day = 'Tue' then col1 end) tue_1,
    max(case when day = 'Tue' then col2 end) tue_2,
    max(case when day = 'Tue' then col3 end) tue_3
from mytable
group by class_id, class

You can then expand the same logic for the other days and columns.

Advanced table pivoting in MySQL and Dynamic Jasper

1 Answers1