CODEWITHSUNDEEP
sqlpostgresqlkey-valuecrosstab
gaston.greco
gaston.greco

Reputation: 23

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

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.

Upvotes: 2

Views: 193

Answers (2)

GMB
GMB

Reputation: 222632

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do conditional aggregation :

select e.id, e.event, e.created_at, 
       max(case when ea.key = 'url' then ea.value end) as url,
       max(case when ea.key = 'product' then ea.value end) as product,
       max(case when ea.key = 'user' then ea.value end) as user
from Events e inner join
     EventAttributes ea
     on ea.event_id = e.id
group by e.id, e.event, e.created_at;

Upvotes: 1

Related Questions