Join master table's data with key-value attributes form another table using keys as columns

Question

I have a PostgreSQL table called Events like this:

+----+----------+------------+
| id | event    | created_at |
+----+----------+------------+
| 1  | pageview | 2019-03-29 |
+----+----------+------------+
| 2  | purchase | 2019-03-28 |
+----+----------+------------+

And another table called EventAttributes

+----------+---------+-------------------------------+
| event_id | key     | value                         |
+----------+---------+-------------------------------+
| 1        | url     | https://www.stackoverflow.com |
+----------+---------+-------------------------------+
| 2        | product | Apple                         |
+----------+---------+-------------------------------+
| 2        | user    | Nick                          |
+----------+---------+-------------------------------+

I want to get all the events along with the associated attributes as columns, like this:

+----+----------+------------+-------------------------------+---------+------+
| id | event    | created_at | url                           | product | user |
+----+----------+------------+-------------------------------+---------+------+
| 1  | pageview | 2019-03-29 | https://www.stackoverflow.com | null    | null |
+----+----------+------------+-------------------------------+---------+------+
| 2  | purchase | 2019-03-29 | null                          | Apple   | Nick |
+----+----------+------------+-------------------------------+---------+------+

I guess I need to use a pivot table but I'm not sure how to do it.

Any help or related article on the topic is welcome.

Answer 1

Use conditional aggregation:

SELECT
    e.id, 
    e.event, 
    e.created_at,
    MAX(CASE WHEN ea.key = 'url' THEN ea.value END) url,
    MAX(CASE WHEN ea.key = 'product' THEN ea.value END) product,
    MAX(CASE WHEN ea.key = 'user ' THEN ea.value END) user 
FROM Events e
INNER JOIN EventAttributes ea ON ea.event_id  = e.id
GROUP BY 
    e.id, 
    e.event, 
    e.created_at