mysqlselectmergegroup-bynested-select

MYSQL Select multiple columns depending on a specific column


I would like to do a SELECT request witch manage to get 2 columns of VALUE (DESKTOP & MOBILE) depending on the PLATFORM value.

Here is an example table:

+----+---------+------+----------+-------+
| ID | PROJECT | NAME | PLATFORM | VALUE |
+----+---------+------+----------+-------+
|  1 |       1 | Foo  | desktop  |     1 |
|  2 |       1 | Foo  | mobile   |    42 |
|  3 |       1 | Bar  | desktop  |     3 |
|  4 |       1 | Bar  | mobile   |    10 |
|  5 |       2 | Foo  | desktop  |     2 |
|  6 |       2 | Bar  | mobile   |     9 |
+----+---------+------+----------+-------+

Desired output:

+---------+------+---------+--------+
| PROJECT | NAME | DESKTOP | MOBILE |
+---------+------+---------+--------+
|       1 | Foo  | 1       | 42     |
|       1 | Bar  | 3       | 10     |
|       2 | Foo  | 2       | NULL   |
|       2 | Bar  | NULL    | 9      |
+---------+------+---------+--------+

What I tried:

SELECT project, name,
(CASE platform WHEN 'desktop' THEN value END) AS "desktop",
(CASE platform WHEN 'mobile' THEN value END) AS "mobile"
FROM test
GROUP BY name, project
ORDER BY project, value ASC

+---------+------+---------+--------+
| project | name | desktop | mobile |
+---------+------+---------+--------+
|       1 | Foo  | 1       | NULL   |
|       1 | Bar  | 3       | NULL   |
|       2 | Foo  | 2       | NULL   |
|       2 | Bar  | NULL    | 9      |
+---------+------+---------+--------+

Solution

  • Try this:

    SELECT project, NAME, MAX(desktop) AS desktop, MAX(mobile) AS mobile FROM (
        SELECT project, NAME,
        (CASE platform WHEN 'desktop' THEN VALUE END) AS "desktop",
        (CASE platform WHEN 'mobile' THEN VALUE END) AS "mobile"
        FROM test
        ) AS aa
    GROUP BY aa.NAME, aa.project
    ORDER BY aa.project
    

    Explanation:

    First you make a selection (aa) of all data, expanding value according the content of platform.

    Then you use that selection as the origin of the grouped data.

    Result:

    project name   desktop mobile
    1       Foo       1       42
    1       Bar       3       10
    2       Foo       2       NULL
    2       Bar       NULL    9