Multiple columns from single table My tables: 

categories:

+----+------+
| id | name |
+----+------+
| 1  | blue |
| 2  | red  |
+----+------+

data:

+----------+----------+-------+
|   date   | category | price |
+----------+----------+-------+
|2018-12-17|     1    |   25  |
|2018-12-17|     2    |   12  |
|2018-12-16|     1    |   27  |
|2018-12-16|     2    |   14  |
+----------+----------+-------+


My objective:

+----------+------+-----+
|   date   |   1  |  2  |
+----------+------+-----+
|2018-12-17|  25  |  12 |
|2018-12-16|  27  |  14 |
+----------+------+-----+


Or better:

+----------+------+-----+
|   date   | blue | red |
+----------+------+-----+
|2018-12-17|  25  |  12 |
|2018-12-16|  27  |  14 |
+----------+------+-----+


My question:

Is there an elegant/recommended/easy query to create the resulting table rather than using unions and one query for each category?
You can use a SUM(CASE) statement and a GROUP BY in this way:

select 
    date,
    sum(case when category = 1 then price else 0 end) as blue,
    sum(case when category = 2 then price else 0 end) as red
from
    data d
group by
    date