How Get MySQl Pivot Select

I Have an query in MySql

select User_ID,`date`,
if(weekday(`date`)=0,'Monday',
if(weekday(`date`)=1,'Tuesday',
if(weekday(`date`)=2,'Wenesday',
if(weekday(`date`)=3,'Thursday',
if(weekday(`date`)=4,'Friday',
if(weekday(`date`)=5,'Saturday',
if(weekday(`date`)=6,'Sunday',false))))))) as `Day`,
`In`,`Out`
from attendance 
where `date` between '2019-11-04' and '2019-11-10'
order by User_ID

Result:

enter image description here

How Can get this result?

enter image description here

Answer

Use conditional aggregation:

select 
    User_ID,
    `date`,
    min(case when weekday(`date`) = 0 then `In` end) monday_in,
    max(case when weekday(`date`) = 0 then `Out` end) monday_out,
    min(case when weekday(`date`) = 1 then `In` end) tuesday_in,
    max(case when weekday(`date`) = 1 then `Out` end) tuesday_out,
    min(case when weekday(`date`) = 2 then `In` end) wednesday_in,
    max(case when weekday(`date`) = 2 then `Out` end) wednesday_out,
    min(case when weekday(`date`) = 3 then `In` end) thursday_in,
    max(case when weekday(`date`) = 3 then `Out` end) thursday_out,
    min(case when weekday(`date`) = 4 then `In` end) friday_in,
    max(case when weekday(`date`) = 4 then `Out` end) friday_out,
    min(case when weekday(`date`) = 5 then `In` end) saturday_in,
    max(case when weekday(`date`) = 5 then `Out` end) saturday_out,
    min(case when weekday(`date`) = 6 then `In` end) sunday_in,
    max(case when weekday(`date`) = 6 then `Out` end) sunday_out
from attendance
where `date` between '2019-11-04' and '2019-11-10'
group by User_ID, `date`
order by User_ID, `date`

Note: in, out and date are reserved words in MySQL; it is not a good idea to use them as column names (you must to use backticks to quote them, which gets tedious in a query such the above).