How to turn columns into rows in mysql

Question

I have a table like this in mySql Database. this is an employee attendance table

Status    Date
P      2021-01-06
A      2021-01-08
P      2021-01-09
P      2021-01-10
A      2021-01-18
P      2021-01-17

I want this table in following format.

2021-01-06     2021-01-08    2021-01-09    2021-01-10     2021-01-18    2021-01-17
    P              A             P             P               A            P

How can I fix this?

so what have you ***tried yourself*** so far? what problems did you encounter, what have you researched? and if it is so urgent: have you considered *paying* someone? — Franz Gleichmann, Jan 18 '21 at 13:11
Why is there a C# tag? Do you need to do the transphormation in C#? — Drag and Drop, Jan 18 '21 at 13:13
Consider handling issues of data display in application code. — Strawberry, Jan 18 '21 at 13:29
@FranzGleichmann I'm new to C# and mysql I don't have any idea how to do that. I use pivot table query but it not work accurately. please help me out — ABDUL REHMAN, Jan 18 '21 at 17:20

score 0 · Answer 1 · answered Jan 18 '21 at 13:11

0

You can use conditional aggregation:

select max(case when date = '2021-01-06' then status end) as x_20210106,
       max(case when date = '2021-01-07' then status end) as x_20210107,
       . . . 
from t;

answered Jan 18 '21 at 13:11

Gordon Linoff

1,198,228
53
572
709

How to turn columns into rows in mysql

1 Answers1