select
	message,
	coalesce(sum(case when ts='20160519' then num end), 0) as `20160519`,
	coalesce(sum(case when ts='20160520' then num end), 0) as `20160520`,
	coalesce(sum(case when ts='20160521' then num end), 0) as `20160521`,
	coalesce(sum(case when ts='20160522' then num end), 0) as `20160522`,
	coalesce(sum(case when ts='20160523' then num end), 0) as `20160523`,
	coalesce(sum(case when ts='20160524' then num end), 0) as `20160524`,
	coalesce(sum(case when ts='20160525' then num end), 0) as `20160525`,
	coalesce(sum(case when ts='20160526' then num end), 0) as `20160526`,
	coalesce(sum(case when ts='20160527' then num end), 0) as `20160527`,
	coalesce(sum(case when ts='20160528' then num end), 0) as `20160528`,
	coalesce(sum(case when ts='20160529' then num end), 0) as `20160529`,
	coalesce(sum(case when ts='20160530' then num end), 0) as `20160530`,
	coalesce(sum(case when ts='20160531' then num end), 0) as `20160531`,
	coalesce(sum(case when ts='20160601' then num end), 0) as `20160601`,
	coalesce(sum(case when ts='20160602' then num end), 0) as `20160602`,
	coalesce(sum(case when ts='20160603' then num end), 0) as `20160603`,
	coalesce(sum(case when ts='20160604' then num end), 0) as `20160604`,
	coalesce(sum(case when ts='20160605' then num end), 0) as `20160605`,
	coalesce(sum(case when ts='20160606' then num end), 0) as `20160606`,
	coalesce(sum(case when ts='20160607' then num end), 0) as `20160607`,
	coalesce(sum(case when ts='20160608' then num end), 0) as `20160608`,
	coalesce(sum(case when ts='20160609' then num end), 0) as `20160609`,
	coalesce(sum(case when ts='20160610' then num end), 0) as `20160610`,
	coalesce(sum(case when ts='20160611' then num end), 0) as `20160611`,
	coalesce(sum(case when ts='20160612' then num end), 0) as `20160612`,
	coalesce(sum(case when ts='20160613' then num end), 0) as `20160613`,
	coalesce(sum(case when ts='20160614' then num end), 0) as `20160614`,
	coalesce(sum(case when ts='20160615' then num end), 0) as `20160615`,
	coalesce(sum(case when ts='20160616' then num end), 0) as `20160616`,
	coalesce(sum(case when ts='20160617' then num end), 0) as `20160617`,
	coalesce(sum(case when ts='20160618' then num end), 0) as `20160618`,
	coalesce(sum(case when ts='20160619' then num end), 0) as `20160619`,
	coalesce(sum(case when ts='20160620' then num end), 0) as `20160620`,
	coalesce(sum(case when ts='20160621' then num end), 0) as `20160621`,
	coalesce(sum(case when ts='20160622' then num end), 0) as `20160622`,
	coalesce(sum(case when ts='20160623' then num end), 0) as `20160623`,
	coalesce(sum(case when ts='20160624' then num end), 0) as `20160624`,
	coalesce(sum(case when ts='20160625' then num end), 0) as `20160625`
from (
	select trim(trailing '\r\n' from event_message) as message, ts, num from (
		select event_message, left(timestamp,8) as ts, count(*) as num
		from UploadWizardExceptionFlowEvent_11772722
		where timestamp > '20160519' and event_message in (
			select event_message 
			from UploadWizardExceptionFlowEvent_11772722
			where timestamp > '20160519'
			group by event_message
			having count(*)>100
		)
		group by event_message, left(timestamp,8)
		union
		select null, left(timestamp,8) as ts, count(*) as num
		from UploadWizardExceptionFlowEvent_11772722
		where timestamp > '20160519' and event_message not in (
			select event_message 
			from UploadWizardExceptionFlowEvent_11772722
			where timestamp > '20160519'
			group by event_message
			having count(*)>100
		)
		group by left(timestamp,8)
	) u
	order by ts, num, message
) v
group by message;
