MYSQL - Group by one column and slip another

Question

I have a table which logs messages sent to different platforms (there are 3) by a user. The same message can be sent to multiple platforms. I want to queue the table by user_id and GROUP BY message_code with each platform having its own column with 1 indicating sent and 0 indicating not sent.

Is this possible to accomplish with a MYSQL SELECT queue?

+------------------------------------+
|id |message_code |platform |user_id |
+------------------------------------+
|1  |2wkdeyu      |1        |256     |
|2  |2wkdeyu      |2        |256     |
|3  |4kdi940      |1        |256     |
|4  |3eji903      |2        |256     |
|5  |4kdi940      |2        |256     |
|6  |4kdi940      |3        |256     |
|7  |6eioio4      |1        |256     |
+------------------------------------+

What I've done.

SELECT message_code,GROUP_CONCAT(platform) AS platforms,user_id FROM message_log WHERE user_id = '256' GROUP BY message_code;

+--------------------------------+
|message_code |platform |user_id |
+--------------------------------+
|2wkdeyu      |1,2      |256     |
|4kdi940      |1,2,3    |256     |
|3eji903      |2        |256     |
|6eioio4      |1        |256     |
+--------------------------------+

But what I want to accomplish is this.

+----------------------------------------------------------+
|message_code |platform 1 |platform 2 |platform 3 |user_id |
+----------------------------------------------------------+
|2wkdeyu      |1          |1          |0          |256     |
|4kdi940      |1          |1          |1          |256     |
|3eji903      |0          |1          |0          |256     |
|6eioio4      |1          |0          |0          |256     |
+----------------------------------------------------------+

Any help would be appreciated.

This is called pivot generation, if the `platform` values are known then its easy else dynamic sql — Abhik Chakraborty, Sep 25 '15 at 10:32
possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Strawberry, Sep 25 '15 at 10:47
Its similar to my question but not the same I'm not counting the values in the column I want to identity them and then return a column based on that value. — LDC, Sep 25 '15 at 11:22
Thanks for your help @AbhikChakraborty, I had almost got there, I was just missing MAX. That is exactly what I was trying to accomplish. — LDC, Sep 25 '15 at 12:30

score 1 · Answer 1 · answered Sep 25 '15 at 12:29

1

Try out this:

select message_code,
        coalesce(max(case when platform=1 then 1 end),0) as `platform 1`,
        coalesce(max(case when platform=2 then 1 end),0) as `platform 2`,
        coalesce(max(case when platform=3 then 1 end),0) as `platform 3`,
        user_id
from message_log
where user_id=256
group by message_code;

You can use case to check for particular platform.

SQL Fiddle Example:http://sqlfiddle.com/#!9/74b1e/3

answered Sep 25 '15 at 12:29

Abhishek Ginani

4,511
4
23
35

This will work, but as a somewhat new to the site, you should also explain how and why so the others have an understanding as opposed to just... here do this. also, you could have simplified by removing the coalesce by just changing to ... "then 1 else 0 end" for each entry. – DRapp Sep 25 '15 at 12:33

MYSQL - Group by one column and slip another

1 Answers1