is there anyway to display single column data into multiple columns

I tried to do it but it was impossible for me. the solution I need to display single column data into multiple column for the current table only. e.g column [status] 3 records {present,late,absent} to display 3 columns and single row, to prevent 3 rows

SELECT tid,status, COUNT(status) AS counter FROM `attend_teacher` GROUP BY status,tid ORDER by tid

+-----+---------+---------+
| tid | status  | counter |
+-----+---------+---------+
|   1 | absent  |       1 |
|   1 | present |       2 |
|   1 | late    |       1 |
|   3 | late    |       1 |
+-----+---------+---------+

The result i want to display like this by grouping tid

+-----+---------+---------+--------+
| tid | Present | late    | absent |
+-----+---------+---------+--------+
|   1 |    2    |    1    |    1   |
+-----+---------+---------+--------+
|   3 |    0    |    1    |    0   |
+-----+---------+---------+--------+

Table structure

SELECT * FROM `attend_teacher`;
+----+-----+----------+---------+---------+----------------+
| id | tid | day      | status  | subject | date_created   |
+----+-----+----------+---------+---------+----------------+
|  1 |   1 | Saturday | present | 1,5     | 8 May 2019     |
|  2 |   1 | Saturday | present | 1,1,5,2 | 8 October 2019 |
|  3 |   1 | Saturday | absent  | 1,1,5,2 | 9 October 2019 |
|  4 |   1 | Saturday | late    | 1,1,5,2 | 9 October 2019 |
|  5 |   3 | Saturday | late    | 1,3,5,4 | 9 October 2019 |
+----+-----+----------+---------+---------+----------------+
5 rows in set (0.00 sec)

Answer

You can use conditional aggregation to pivot the dataset:

select
    tid,
    sum(case when status = 'present' then 1 else 0 end) present,
    sum(case when status = 'late' then 1 else 0 end) late,
    sum(case when status = 'absent ' then 1 else 0 end) absent 
from attend_teacher
group by tid

Advertisement

Answer