landpack landpack - 22 days ago 7
MySQL Question

How to do a rotation with sql query result?

I want my query result rotation by

level
field! Currently, my sql is simple group by two filed! as below show:

SELECT uid, level, count(uid) as counter
FROM user_log
GROUP BY uid, level;


+-------+----------+---------+
| uid | level | counter |
+-------+----------+---------+
| 101 | 2 | 1890 |
| 101 | 1 | 230 |
| 102 | 2 | 430 |
| 102 | 1 | 30 |
+-------+----------+---------+

How do i make the result show as below?

+-------+----------+---------+
| uid | cunter1 | counter2|
+-------+----------+---------+
| 101 | 230 | 1890 |
| 102 | 30 | 430 |
+-------+----------+---------+

Answer Source

For only 2 levels:

SELECT t.uid, 
       MAX(CASE WHEN t.level = 1 THEN t.counter END) counter1,
       MAX(CASE WHEN t.level = 2 THEN t.counter END) counter2
FROM (SELECT uid, level, count(uid) as counter
        FROM user_log
       GROUP BY uid, level) t
GROUP BY t.uid
ORDER BY t.uid;