Btukfyl

There is a Postgres database and the table has three columns. The data structure is in external system so I can not modify it.

Every object is represented by three rows (identified by column element_id - rows with the same value in this column represents the same object), for example:

key     value            element_id

-----------------------------------

status  active           1

name    exampleNameAAA   1

city    exampleCityAAA   1

status  inactive         2

name    exampleNameBBB   2

city    exampleCityBBB   2

status  inactive         3

name    exampleNameCCC   3

city    exampleCityCCC   3

I want to get all values describing every objects (name, status and city).

For this example the output should be like:

exampleNameAAA   | active    | exampleCityAAA

exampleNameBBB   | inactive  | exampleCityBBB

exampleNameCCC   | inactive  | exampleCityCCC   

I know how to join two rows:

select a.value as name,

       b.value as status

from the_table a 

  join the_table b 

    on a.element_id = b.element_id 

   and b."key" = 'status'

where a."key" = 'name';

How is it possible to join three columns?

You can try below

DEMO

select a.value as name,

       b.value as status,c.value as city

from t1 a 

  join t1 b 

    on a.element_id = b.element_id and b."keys" = 'status'

    join t1 c on a.element_id = c.element_id and c."keys" = 'city'

where a."keys" = 'name';

OUTPUT

name            status      city

exampleNameAAA  active      exampleCityAAA

exampleNameBBB  inactive    exampleCityBBB

exampleNameCCC  inactive    exampleCityCCC

One option is to simply add another join for each value you need (this is one of the big disadvantages of the EAV (anti) pattern you are using:

select a.value as name,

       b.value as status, 

       c.value as city

from the_table a 

  join the_table b on a.element_id = b.element_id and b."key" = 'status'

  join the_table c on a.element_id = c.element_id and c."key" = 'city'

where a."key" = 'name';

Another option is to aggregate all key/value pairs for an element into a JSON then you can easily access each one without additional joins:

select t.element_id, 

       t.obj ->> 'city' as city, 

       t.obj ->> 'status' as status,

       t.obj ->> 'name' as name

from (

  select e.element_id, jsonb_object_agg("key", value) as obj

  from element e

  group by e.element_id

) t;

If the table is really big this might be a lot slower than the join version due to the aggregation step. If you limit the query to only some elements (e.g. by adding a where element_id = 1 or where element_id in (1,2,3)) then this should be quite fast.

It has the advantage that you always have all key/value pairs for each element_id available regardless on what you do. The inner select could be put into a view, to make things easier.

Online example: https://rextester.com/MSZOWU37182

Seems like you want to PIVOT

One way to do that is via conditional aggregation.

select 

 -- t.element_id,

 max(case when t.key = 'name' then t.value end) as name,

 max(case when t.key = 'status' then t.value end) as status,

 max(case when t.key = 'city' then t.value end) as city

from the_table t

group by t.element_id;

db<>fiddle here

Or use crosstab:

select 

-- element_id,

name, 

status, 

city

from crosstab (

   'select t.element_id, t.key, t.value 

   from the_table t'

) as ct (element_id int, name varchar(30), status varchar(30), city varchar(30));

But if you do like those joins, here's a way

select 

-- el.element_id,

nm.value as name,

st.value as status,

ci.value as city

from 

(

  select distinct t.element_id 

  from the_table t

  where t.key in ('name','status','city')

) as el

left join the_table as nm on (nm.element_id = el.element_id and nm.key = 'name')

left join the_table as st on (st.element_id = el.element_id and st.key = 'status')

left join the_table as ci on (ci.element_id = el.element_id and ci.key = 'city');