CODEWITHSUNDEEP
phpmysqlpivot-table
snowflakes74
snowflakes74

Reputation: 1307

Pivot Table in MySQL not returning correctly

I know this question have been asked few times and I am trying to do exactly what the accepted answers are but obviously I am doing something wrong and can not get the desired output.

I would like to return data in following format:

Name | 27-06-2015 | 28-06-2015

===== | ========== | ==========

John | 6:30AM | 7:30 AM

Steve | 6:00 AM | 4:00 AM

++++++++++++++++++++++++++++++++++++++

but when I run the Fiddle query I do not get any data for the next day in the attendance table. If I remove grouping I get the data for the next day but a new row is inserted in the resultset:

Name | 27-06-2015 | 28-06-2015

===== | ========== | ==========

John | 6:30AM | null

John | null | 7:30 AM

Steve | 6:00 AM | null

Steve | null | 4:00 AM

++++++++++++++++++++++++++++++++++

The sql I am using is :

SELECT 
 Name,
 case(dropoffdate) when '20150627' then CONCAT(DropoffTime, ' - ', PickupTime) end as '2015-06-27',
 case (DropoffDate) when '20150628' then CONCAT(DropoffTime, ' - ', PickupTime) end as '2015-06-28'
 FROM
 Student s
 LEFT OUTER JOIN Attendance a on s.id = a.student 
 GROUP BY s.id

Here is the fiddle link

Upvotes: 2

Views: 45

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24012

You have to use MAX or MIN group function as desired, to fetch valid record. 

SELECT 
       Name,
       MAX( case(dropoffdate) 
                 when '20150627' 
                      then CONCAT(DropoffTime, ' - ', PickupTime) 
            end ) as '2015-06-27',
       MAX( case (DropoffDate) 
                 when '20150628' 
                      then CONCAT(DropoffTime, ' - ', PickupTime) 
            end ) as '2015-06-28'
 FROM Student s
 LEFT OUTER JOIN Attendance a on s.id = a.student 
 GROUP BY s.id

Upvotes: 1

Related Questions