CIVICRM Contact Export

DROP TEMPORARY TABLE IF EXISTS contact_report;
DROP TEMPORARY TABLE IF EXISTS contact_report_address;
DROP TEMPORARY TABLE IF EXISTS contact_report_group;

CREATE TEMPORARY TABLE contact_report_group SELECT gc.contact_id, GROUP_CONCAT(DISTINCT g.name ORDER BY g.name ASC SEPARATOR ", ") as groups FROM civicrm_group_contact gc LEFT JOIN civicrm_group g ON gc.group_id=g.id GROUP BY gc.contact_id;

CREATE TEMPORARY TABLE contact_report_address SELECT a.contact_id, a.street_address, a.city, a.postal_code, sp.abbreviation as state FROM civicrm_address a LEFT JOIN civicrm_state_province sp ON a.state_province_id=sp.id GROUP BY a.contact_id;

CREATE UNIQUE INDEX idx ON contact_report_address (contact_id); 

CREATE UNIQUE INDEX idx ON contact_report_group (contact_id); 

CREATE TEMPORARY TABLE contact_report SELECT c.id, c.display_name, c.source, c.first_name, c.last_name, c.job_title, o.organization_name as employer, p.phone, e.email, a.street_address, a.city, a.state, a.postal_code, g.groups FROM civicrm_contact c LEFT JOIN civicrm_phone p ON c.id=p.contact_id LEFT JOIN civicrm_email e ON c.id = e.contact_id LEFT JOIN contact_report_address a ON c.id=a.contact_id LEFT JOIN civicrm_contact o ON c.employer_id=o.id LEFT JOIN contact_report_group g ON c.id = g.contact_id GROUP BY c.id;

SELECT * FROM contact_report c;