Severus Tux Severus Tux - 23 days ago 8
SQL Question

Changing view of a table : rows to columns

I am writing a program to schedule timetable of a school. I am using

QSQLITE
and c++ in Qt.

I have a table as shown below. The values are given by the user.

+------+------+------------------+
| day | hour | sub |
+------+------+------------------+
| Mon | 1 | Maths |
| Mon | 2 | Physics |
| Mon | 3 | Chemistry |
| Tue | 1 | Biology |
| Tue | 2 | P.H.E |
| Tue | 3 | Computer Science |
| Wed | 1 | Physics |
| Wed | 2 | Chemistry Lab |
| Wed | 3 | Chemistry Lab |
+------+------+------------------+


I would like to change the view to something like this :

+------+----------+-----------------+---------------------+
| day | 1 | 2 | 3 |
+------+----------+-----------------+---------------------+
| Mon | Maths | Physics | Chemistry |
| Tue | Biology | P.H.E | Computer Science |
| Wed | Physics | Chemistry Lab | Chemistry Lab |
+------+------+---------------------+---------------------+


Is it possible is any way ?

NOTE : If it's not possible by only using SQL commands, I am Ok with C++.

Thanks.

Answer

You can use conditional aggregation:

select day,
       max(case when hour = 1 then sub end) as hour_1,
       max(case when hour = 2 then sub end) as hour_2,
       max(case when hour = 3 then sub end) as hour_3
from t
group by day;