Why i am not getting proper output while create pivot table using mysql

Issue

i am trying to get record based on on date range and get data in pivot table format
but maybe something wrong, only one day data getting less day record not coming

MySQL QUERY-

SELECT number,
if(date= '2022-08-22', status,  null) day1_status,
if(date= '2022-08-23', status,  null) day2_status,
if(date= '2022-08-24', status,  null) day3_status,
if(date= '2022-08-25', status,  null) day4_status
from table where date BETWEEN '2022-08-22' and '2022-08-25'
GROUP by number

output –

number day1_status day2_status day3_status day4_status
10 active null null null
20 active null null null

above mention output is wrong my expected output is-

number day1_status day2_status day3_status day4_status
10 active no active no
20 active active no active

Current mysql table.

id number status date
1 10 active 2022-08-22
2 10 no 2022-08-23
3 10 active 2022-08-24
4 10 no 2022-08-25
5 20 active 2022-08-22
6 20 active 2022-08-23
7 20 no 2022-08-24
8 20 active 2022-08-25

Solution

You are using GROUP BY without an aggregate function.

SELECT 
  number,
  max(if(`date`= '2022-08-22', status,  null)) day1_status,
  max(if(`date`= '2022-08-23', status,  null)) day1_status,
  max(if(`date`= '2022-08-24', status,  null)) day1_status,
  max(if(`date`= '2022-08-25', status,  null)) day1_status
from `table` where date BETWEEN '2022-08-22' and '2022-08-25'
GROUP by number

See dbfiddle

Answered By – slaakso

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published