Cacher is the code snippet organizer for pro developers

select avm_location_rollup, 

sum( case when cti_service_category = 'Events Support' then tickets_curr else 0 end ) as tickets_curr_events,
sum( case when cti_service_category = 'Events Support' then tickets_1m else 0 end ) as tickets_1m_events,
sum( case when cti_service_category = 'Events Support' then tickets_2m else 0 end ) as tickets_2m_events,
sum( case when cti_service_category = 'Events Support' then tickets_3m else 0 end ) as tickets_3m_events,

sum( case when cti_service_category = 'Maintenance' then tickets_curr  else 0  end ) as tickets_curr_maintenance,
sum( case when cti_service_category = 'Maintenance' then tickets_1m  else 0  end ) as tickets_1m_maintenance,
sum( case when cti_service_category = 'Maintenance' then tickets_2m  else 0  end ) as tickets_2m_maintenance,
sum( case when cti_service_category = 'Maintenance' then tickets_3m  else 0  end ) as tickets_3m_maintenance,


sum( case when cti_service_category = 'Moves, Adds, Changes' then tickets_curr  else 0  end ) as tickets_curr_mac,
sum( case when cti_service_category = 'Moves, Adds, Changes' then tickets_1m  else 0  end ) as tickets_1m_mac,
sum( case when cti_service_category = 'Moves, Adds, Changes' then tickets_2m  else 0  end ) as tickets_2m_mac,
sum( case when cti_service_category = 'Moves, Adds, Changes' then tickets_3m  else 0  end ) as tickets_3m_mac,

sum( case when cti_service_category = 'Other' then tickets_curr  else 0  end ) as tickets_curr_other,
sum( case when cti_service_category = 'Other' then tickets_1m  else 0  end ) as tickets_1m_other,
sum( case when cti_service_category = 'Other' then tickets_2m  else 0  end ) as tickets_2m_other,
sum( case when cti_service_category = 'Other' then tickets_3m  else 0  end ) as tickets_3m_other,

sum( case when cti_service_category = 'Preventative Maintenance' then tickets_curr  else 0  end ) as tickets_curr_prev_maint,
sum( case when cti_service_category = 'Preventative Maintenance' then tickets_1m  else 0  end ) as tickets_1m_prev_maint,
sum( case when cti_service_category = 'Preventative Maintenance' then tickets_2m  else 0  end ) as tickets_2m_prev_maint,
sum( case when cti_service_category = 'Preventative Maintenance' then tickets_3m  else 0  end ) as tickets_3m_prev_maint,

sum( case when cti_service_category = 'Project' then tickets_curr  else 0  end ) as tickets_curr_proj,
sum( case when cti_service_category = 'Project' then tickets_1m  else 0  end ) as tickets_1m_proj,
sum( case when cti_service_category = 'Project' then tickets_2m  else 0  end ) as tickets_2m_proj,
sum( case when cti_service_category = 'Project' then tickets_3m  else 0  end ) as tickets_3m_proj,


from (
select avm_location_rollup, cti_service_category,
sum(case when period_rank = 1 then ticket_count else 0 end ) as tickets_curr,
sum(case when period_rank = 2 then ticket_count else 0 end ) as tickets_1m,
sum(case when period_rank = 3 then ticket_count else 0 end ) as tickets_2m,
sum(case when period_rank = 4 then ticket_count else 0 end ) as tickets_3m,

from ( select * from (SELECT avm_location_rollup,cti_service_category,resolved_year_month,
DENSE_RANK() OVER (ORDER BY resolved_year_month DESC) period_rank,
sum( ticket_count ) ticket_count
FROM [google.com:bi-metrics:rramona_avm_v2.guts_resolved_detail_trailing_year]
GROUP BY 1,2,3 ORDER BY 3 DESC, 1, 2 )
where period_rank in ( 1,2,3,4 ) ) group by 1,2 ) 
group by 1