Cacher is the code snippet organizer for pro developers

SELECT
  wp_posts.post_title,post_meta.post_id,post_meta.meta_key, post_meta.meta_value,
  /* if col1 matches the name string of this CASE, return post_meta.meta_key, otherwise return NULL */
  /* Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
  MAX(CASE WHEN (post_meta.meta_key = '_facility_address') THEN post_meta.meta_value ELSE NULL END) AS facility_address,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_city') THEN post_meta.meta_value ELSE NULL END) AS facility_city,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_state') THEN post_meta.meta_value ELSE NULL END) AS facility_state,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_zip') THEN post_meta.meta_value ELSE NULL END) AS facility_zip,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_website') THEN post_meta.meta_value ELSE NULL END) AS facility_website,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_assistant_email') THEN post_meta.meta_value ELSE NULL END) AS facility_assistant_email,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_assistant_fax') THEN post_meta.meta_value ELSE NULL END) AS facility_assistant_fax,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_assistant_first') THEN post_meta.meta_value ELSE NULL END) AS facility_assistant_first_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_assistant_last') THEN post_meta.meta_value ELSE NULL END) AS facility_assistant_last_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_assistant_phone') THEN post_meta.meta_value ELSE NULL END) AS facility_assistant_phone,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_admissions_email') THEN post_meta.meta_value ELSE NULL END) AS facility_admissions_email,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_admissions_fax') THEN post_meta.meta_value ELSE NULL END) AS facility_admissions_fax,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_admissions_phone') THEN post_meta.meta_value ELSE NULL END) AS facility_admissions_phone,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_admissions_last') THEN post_meta.meta_value ELSE NULL END) AS facility_admissions_last_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_admissions_first') THEN post_meta.meta_value ELSE NULL END) AS facility_admissions_first_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_marketing_email') THEN post_meta.meta_value ELSE NULL END) AS facility_marketing_email,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_marketing_fax') THEN post_meta.meta_value ELSE NULL END) AS facility_marketing_fax,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_marketing_phone') THEN post_meta.meta_value ELSE NULL END) AS facility_marketing_phone,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_marketing_last') THEN post_meta.meta_value ELSE NULL END) AS facility_marketing_last_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_marketing_first') THEN post_meta.meta_value ELSE NULL END) AS facility_marketing_first_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_ceo_email') THEN post_meta.meta_value ELSE NULL END) AS facility_ceo_email,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_ceo_fax') THEN post_meta.meta_value ELSE NULL END) AS facility_ceo_fax,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_ceo_phone') THEN post_meta.meta_value ELSE NULL END) AS facility_ceo_phone,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_ceo_last') THEN post_meta.meta_value ELSE NULL END) AS facility_ceo_last_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_ceo_first') THEN post_meta.meta_value ELSE NULL END) AS facility_ceo_first_name,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_country') THEN post_meta.meta_value ELSE NULL END) AS facility_country,
  MAX(CASE WHEN (post_meta.meta_key = '_facility_associate') THEN post_meta.meta_value ELSE NULL END) AS facility_associate
  
 
FROM `wp_posts` JOIN `wp_postmeta` as post_meta on post_meta.post_id = wp_posts.ID
WHERE wp_posts.post_type = 'facility' AND wp_posts.post_status = 'publish' AND post_meta.meta_key LIKE '_facility%' 
GROUP BY post_meta.post_id
ORDER BY post_meta.post_id