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