Join multiple row results into one

Question

I have a problem with combining multiple rows result into one row result by userID.

I get this result:

||userID||eventTypeID||COUNT(*)||  
||  1   ||    1      ||    1   ||  
||  1   ||    2      ||    1   ||  
||  2   ||    1      ||    1   ||   
||  3   ||    2      ||    1   ||

What I would like to is get it this way:

||userID||eventTypeID ONE||COUNT()||eventTypeID TWO||COUNT()||

and so on...

I cant figure out how I do this, I have tried pivotSQL and concat, but I can't make it work.

My SQL query:

SELECT eventMatch.userID, eventMatch.eventTypeID, COUNT( * )   
FROM  `eventMatch` , activity, activityMatch  
WHERE eventMatch.activityID = activity.activityID  
AND activity.activityID = activityMatch.activityID  
AND activity.khID =1  
GROUP BY eventTypeID, userID  
ORDER BY userID ASC  

Hopes somebody can help, maybe it is easy, but I have tried looking for solutions for 2 days now:-)

Thanks..

Answer 1

Try this:

SELECT
  eventMatch.userID, 
  SUM(eventMatch.eventTypeID = 1) As TypeOne,
  SUM(eventMatch.eventTypeID = 2) As TypeTwo, 
  COUNT( * )   
FROM  `eventMatch` 
INNER JOIN activity      ON eventMatch.activityID = activity.activityID 
INNER JOIN activityMatch ON activity.activityID = activityMatch.activityID  
WHERE activity.khID =1  
GROUP BY userID  
ORDER BY userID ASC;

---

Update 1

For multiple event types, you can do this dynamically like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(em.eventTypeID = ''',
      em.eventTypeID, ''', 1, 0)) AS Type',
      em.eventTypeID )
  ) INTO @sql
FROM  `eventMatch`       AS em;

SET @sql = CONCAT('SELECT em.userID, ', @sql,
                  ', COUNT( * )   
                  FROM  `eventMatch`       AS em
                  INNER JOIN activity      AS  a ON em.activityID = a.activityID 
                  INNER JOIN activityMatch AS am ON a.activityID = am.activityID  
                  GROUP BY em.userID  
                  ORDER BY em.userID ASC  ;');

prepare stmt 
FROM @sql;

execute stmt;

SQL Fiddle Demo

---

Update 2

If you want to iclude all the types from the EventTypes table, even if this type has no matches in the others table, you should get the list of types dynamically in the firs step from this table EventTypes instead of getting them from the table eventmatch then do the dynamic query the same way as before. Like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(em.eventTypeID = ''',
      em.eventTypeID, ''', 1, 0)) AS Type',
      em.eventTypeID )
  ) INTO @sql
FROM  eventTypes       AS em; -- <---------- this is what I changed.


SET @sql = CONCAT('SELECT em.userID, ', @sql,
                  ', COUNT( * )   
                  FROM  `eventMatch`       AS em
                  INNER JOIN activity      AS  a ON em.activityID = a.activityID 
                  INNER JOIN activityMatch AS am ON a.activityID = am.activityID  
                  GROUP BY em.userID  
                  ORDER BY em.userID ASC  ;');

prepare stmt 
FROM @sql;

execute stmt;

Updated SQL Fiddle Demo