I have a few tables with data and I am attempting to generate a pivot like table in the following format:
Area | Type | 2013-08-25 | 2013-08-26 | 2013-08-27, etc.. South | Red | 5 | 2 | 9 North | Blue | 3 | 0 | 7
Currently to generate this I am using PHP to loop through each date and generate the sum(if statements for each date based upon a given start & end date.
I know I could run the query and group by date before and then assemble it in PHP but an SQL solution seems easier than that.
Is this the best way to accomplish what I am trying to do or is there another easier way I am missing?
Example Query:
SELECT
iw.display_name as Area,
iet.type as 'Type',
sum(if(date(iac.created_at) = '2013-08-25',1,0)) as '2013-08-25',
sum(if(date(iac.created_at) = '2013-08-26',1,0)) as '2013-08-26',
sum(if(date(iac.created_at) = '2013-08-27',1,0)) as '2013-08-27',
sum(if(date(iac.created_at) = '2013-08-28',1,0)) as '2013-08-28',
sum(if(date(iac.created_at) = '2013-08-29',1,0)) as '2013-08-29',
sum(if(date(iac.created_at) = '2013-08-30',1,0)) as '2013-08-30',
sum(if(date(iac.created_at) = '2013-08-31',1,0)) as '2013-08-31',
count(iac.id) as total
FROM iac
JOIN ioc on ioc.id = iac.ioc_id
JOIN iet on iet.id = ioc.iet_id
JOIN iw on iw.id = iac.iw_id
WHERE date(iac.created_at) between '2013-08-25' and '2013-08-31'
GROUP BY iw.id, iet.id
ORDER BY iw.display_name, iet.type