userid data_type, timespentaday
1 League of Legends 500
1 Hearthstone 1500
1 Hearthstone 1400
2 World of Warcraft 1200
1 Dota 2 100
2 Final Fantasy 500
1 Dota 2 700
User League Of Legends Hearthstone World of Warcraft Dota 2
1 500 1500 0 700
2 0 0 1200 0
SELECT t1.* FROM user_info GROUP BY userid JOIN(
(SELECT max(timespentaday) where data_type='League of Legends'),
(SELECT max(timespentaday) where data_type='Hearhstone'),
(SELECT max(timespentaday) where data_type='Dota 2)'
FROM socialcount AS t2
) as t2
ON t1.userid = t2.userid
basically to do this you need the greatest n per group.. there is a good article on it but the gist is in mysql you have to use variables to even get close to this.. especially with doing a pivot on the table (a fake pivot since MySQL doesn't have native support for that).
SELECT userid, MAX(CASE WHEN data_type = "League of Legends" THEN timespentaday ELSE 0 END) as "League of Legends", MAX(CASE WHEN data_type = "Hearthstone" THEN timespentaday ELSE 0 END) as "Hearthstone", MAX(CASE WHEN data_type = "Dota 2" THEN timespentaday ELSE 0 END) as "Dota 2", MAX(CASE WHEN data_type = "World of Warcraft" THEN timespentaday ELSE 0 END) as "World of Warcraft", MAX(CASE WHEN data_type = "Final Fantasy" THEN timespentaday ELSE 0 END) as "Final Fantasy" FROM ( SELECT *, @A := if(@B = userid, if(@C = data_type, @A + 1, 1), 1) as count_to_use, @B := userid, @C := data_type FROM ( SELECT userid, timespentaday, data_type FROM gamers CROSS JOIN(SELECT @A := 0, @B := 0, @C := '') temp ORDER BY userid ASC, data_type ASC, timespentaday DESC ) t HAVING count_to_use = 1 )t1 GROUP BY userid
MySQL DOCS is quite clear on warnings about using user defined variables:
As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.