Complex pivoting in mysql

Question

I have a table in MySQL that has data and I want to achieve some sort of pivoting from the table, which has become complicated for me. I have tried looking into this but nothing seems to work for me. This is the structure of my table :

roomid| day| 1 | 2 | 3 | 4 | 5 | 6 |
------+----+---+---+---+---+---+---+
1     | 1  |BIO|   |   |   |   |   |
1     | 2  |   |CHE|   |   |   |   |
1     | 3  |   |   |   |   |   |ENG|     
1     | 4  |   |   |KIS|   |   |   |
1     | 5  |   |   |   |   |   |PHY|  
2     | 1  |BIO|   |   |   |   |   | 
2     | 2  |   |CHE|   |   |   |   |
2     | 3  |   |   |   |   |ENG|   |     
2     | 4  |   |   |KIS|   |   |   | 
2     | 5  |   |   |   |   |   |PHY|

This table is holding timetable data, the roomid is id for rooms and the day is days from monday to friday (1 to 5). The columns 1 to 6 are period ids. I need to organize the data to achieve results that show period ids for each class, each day. something like this :

|roomid| 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 | 1 | 2 | 3 | 4 | 5 | 6 |
-------+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+
1      |BIO|   |   |   |   |   |   |CHE|   |   |   |   |   |   |   |   |   |ENG|   |   |KIS|   |   |   |   |   |   |   |   |PHY|
2      |BIO|   |   |   |   |   |   |CHE|   |   |   |   |   |   |   |   |   |ENG|   |   |KIS|   |   |   |   |   |   |   |   |PHY|

Kindly notice that the period ids repeat themselves for different days.

Seriously consider a) revising your schema, and b) handling issues of data display in application code. — Strawberry, Jan 20 '20 at 15:53
@Strawberry I have tried restructuring the tables in my DB, I've been working on this project for 1 and a half months now, this is the easiest solution that can work easily with theproject. — Benson Kiprono, Jan 20 '20 at 15:58

score 1 · Accepted Answer · answered Jan 20 '20 at 15:46

1

You can use conditional aggreagtion:

select room_id,
       max(case when day = 1 then slot_1 end) as day_1_slot_1,
       max(case when day = 1 then slot_2 end) as day_1_slot_2,
       . . . 
       max(case when day = 2 then slot_1 end) as day_2_slot_1,
       max(case when day = 2 then slot_2 end) as day_2_slot_2,
       . . . 
from schedule s
group by room_id

answered Jan 20 '20 at 15:46

Gordon Linoff

1,242,037
58
646
786

Let me try implementing this, I will let you know if it works. – Benson Kiprono Jan 20 '20 at 15:59

score 1 · Answer 2 · answered Jan 20 '20 at 16:25

While not claiming to be a definitive solution, a normalised design might be somewhat as follows. Frankly, it stretches credulity to suggest that the present design is somehow more appropriate than this.

+--------+-----+------+---------+
| roomid | day | slot | subject |
+--------+-----+------+---------+
|      1 |   1 |    1 | BIO     |
|      2 |   1 |    2 | BIO     |
|      1 |   2 |    2 | CHE     |
|      2 |   2 |    2 | CHE     |
|      1 |   4 |    3 | KIS     |
|      2 |   4 |    3 | KIS     |
|      2 |   3 |    5 | ENG     |
|      1 |   3 |    6 | ENG     |
|      1 |   5 |    6 | PHY     |
|      2 |   5 |    6 | PHY     |
+--------+-----+------+---------+

Complex pivoting in mysql

2 Answers2