sql - MySQL select command / pivot table -

i trying produce report has pivot table in it. other writing few sql statements, , doing long hand through code (excel / vba) , loops, can't seem anywhere trying build all-encompassing sql statement.

i have listed example tables below, , example of output @ end.

basically need find contact in group, , display either total occurrences or true false whether in group or not.

so, groups in groups table list contacts out indication (count etc) of them having group in contact_groups table.

is possible single statement, or better sequentially running through records. produce ridiculous amount of sql calls.

tables:  groups ------------------- | key_id | group  | ------------------- |  1     | group1 | |  2     | group2 | |  3     | group3 | |  4     | group4 | |  5     | group5 |  contacts                                           ------------------------------------------------   | key_id | account_code | first_name | surname |   ------------------------------------------------   |  1     | abc001       | john       | smith   |   |  2     | abc001       | philip     | doe     |   |  3     | abc002       | peter      | thomas  |    contact_groups --------------------------------------------------- | key_id | contact_id | group_code | account_code | --------------------------------------------------- |  1     | 1          | group1     | abc001       | |  2     | 1          | group2     | abc001       | |  3     | 2          | group1     | abc001       | |  4     | 3          | group2     | abc002       |  output req'd ----------------------------------------------------------------------- | name         | account | group1 | group2 | group3 | group4 | group5 |  ----------------------------------------------------------------------- | john smith   | abc001  | 1      | 1      |        |        |        | | philip doe   | abc001  | 1      |        |        |        |        | | peter thomas | abc002  |        | 1      |        |        |        | 

sorry size of post, wanted make clear tables working with.

any appreciated.

you can use example:

select c.firstname + ' ' c.surname, account     if(g.group_code='group1',1,null) group1,     if(g.group_code='group2',1,null) group2,     if(g.group_code='group3',1,null) group3,     if(g.group_code='group4',1,null) group4,     if(g.group_code='group5',1,null) group5 groups g inner join contact_groups cg         on g.group = cg.group -- not sure, guess use key_id example isn't clear should use on place inner join contacts c         on c.contact_id = cg.contact_id group c.firstname, c.surname, c.account 

well mentioned in code, i'm not sure if join g.group = cg.group good. try join key column key_id. mentioned output given table content shows won't give expected result. maybe it's bug in expected content demo.