Flattenning the Left Join outcome in PostgreSQL

Question

I have eventtags, filtervalues.So I have something like:

eventtags:
event_id, key_id, value_id, event_date

filtervalues:
value_id, key,value, counts_seen

Let's say I've 2 events reporting with multiple key, value pairs in eventtags table

event_id | key_id | value_id | event_date
---------+--------+----------+-----------
1        |   20   |    32    | xx-xx-xxxx
1        |   21   |    34    | xx-xx-xxxx
2        |   20   |    35    | yy-yy-yyyy
2        |   21   |    39    | yy-yy-yyyy

Corresponding filter_value table is having data as below

values_id |  key  | value | counts_seen
----------+-------+-------+----------
32        | type  | staff | 52
34        | tag   | tag1  | 13
35        | type  | user  | 10
39        | tag   | tag2  | 35

Now based on this I tried below query to consolidate the data from two tables

SELECT t.event_id as Event_Id,
          DATE (t.event_date) as Event_Date,
          v.key as Keys,
          v.value as Values
   FROM eventtags t
   LEFT JOIN filtervalues as v ON t.value_id = v.id

This results in something like this

Event_Id |  Keys  |  Values  | Event_Date
---------+--------+----------+-----------
1        |  type  |   staff  | xx-xx-xxxx
1        |  tag   |   tag1   | xx-xx-xxxx
2        |  type  |   user   | yy-yy-yyyy
2        |  tag   |   tag2   | yy-yy-yyyy

I want the data to be in the below format

Event_Id |  type  |   tag   | Event_Date
---------+--------+---------+-----------
1        | staff  |  tag1   | xx-xx-xxxx
2        | user   |  tag2   | yy-yy-yyyy

What changes do I need to make on the query above to obtain this format?

Note: I cannot use Pivots since the system I'm working on, doesn't support them.

Any help is much appreciated

Answer 1

Try this for your scenario without pivot(crosstab):

SELECT t.event_id as Event_Id,
          max(v.value) filter (where v.key='type') as "type",
          max(v.value) filter (where v.key='tag') as "tag",
          DATE (t.event_date) as Event_Date
   FROM eventtags t
   LEFT JOIN filtervalues as v ON t.value_id = v.id
   group by t.event_id,t.event_date

DEMO

above will work only for PostgreSQL 9.4 and above.