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 |
+---------+------+---------+--------+
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