user1283333 user1283333 - 2 months ago 13
MySQL Question

How to transpose mysql table data using mysql query

I would like to transpose attendance sql table as below

Below Mysql table data

To the following

enter image description here

Please help me here

Thanks
Sara

Answer

In MySQL, you would do this using conditional aggregation:

select pname,
       sum(case when weekday = 'Monday' then days else 0 end) as M,
       . . .,
       sum(days) as Total
from  t
group by pname;

Put the logic in the for the rest of the weekdays where . . . is.

Comments