0

I am trying to produce a report that effectively has a pivot table in it. Other than writing a few sql statements, and doing it long hand through code (Excel / VBA) and loops, I can't seem to get anywhere trying to build an all-encompassing sql statement.

I have listed example tables below, and an example of what I would like to output at the end.

Basically I need to find which contact is in which group, and display either the total occurrences or a true false as to whether they are in the group or not.

So, for all groups in the groups table list all contacts out with an indication (count etc) of them having the group in the contact_groups table.

Is this even possible with a single statement, or would I be better sequentially running through the records. Obviously this could produce a 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 for the size of the post, I just wanted to make it clear what tables I was working with.

Any help would be appreciated.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Martin Trezise
  • 1
  • 3

1 Answers1

1

You can use this for example:

SELECT c.firstname + ' ' c.surname, account
    if(g.group_code='group1',1,null) as group1,
    if(g.group_code='group2',1,null) as group2,
    if(g.group_code='group3',1,null) as group3,
    if(g.group_code='group4',1,null) as group4,
    if(g.group_code='group5',1,null) as group5
FROM groups g
INNER JOIN contact_groups cg
        ON g.group = cg.group -- not sure, would guess to use key_id but your example isn't clear that this should be use on this place
INNER JOIN contacts c
        ON c.contact_id = cg.contact_id
GROUP BY c.firstname, c.surname, c.account

Well as mentioned in the code, I'm not sure if the join with g.group = cg.group would be good. I would normally try to join about a key column like key_id. But the mentioned output with the given table content shows that this won't give the expected result. Maybe it's just a bug in the expected content demo.

Ionic
  • 3,884
  • 1
  • 12
  • 33
  • Well it's just a join? And he asked how to pivot it. I'm sure he would get the joins by itself, as they are easy at all. :-) – Ionic Jun 19 '15 at 13:30
  • don't take the shortcut... others who may have similar may NOT know of the simplicity of joins. Giving a more complete answer will help out not just this one person, but others long term. Don't complete the example, I'll post myself and down-vote your answer. – DRapp Jun 19 '15 at 15:50
  • I can complete it but not now as I'm busy for the next hours. Otherwise I would completed it as you could see in my other 40 answers today. – Ionic Jun 19 '15 at 16:39
  • @DRapp Well I invested the 4 minutes just to satisfy you. xD – Ionic Jun 19 '15 at 16:49
  • I upvoted as a more complete answer... and it was not intended to just satisfy me, but for others who may be in an early learning stage of SQL and don't really understand all the concepts. Why go to multiple solutions only because there was half a solution offered. Thank you though, I sincerely appreciate the update. – DRapp Jun 19 '15 at 16:53
  • Thanks for your help. How about if the group_code is not known, i.e. there could be any number of groups, which could be called anything, not just group1, group2 etc. – Martin Trezise Jun 19 '15 at 22:17
  • Yes you can try a solution like described here: http://stackoverflow.com/a/30932929/4995458. You can use a row_number and try it that way. I'm not sure if MySQL allows dynamic sql - I think no. Otherwise you need to extend the query if you add a new group (if this won't happen to often). Sorry if this won't help much. – Ionic Jun 20 '15 at 05:40