How to transpose a single key-value table in Postgres?

Question

I have. Key-Value table of two columns. I want to get keys as column names and values in individual columns (named as were keys named before). How to do such thing in Postgres ?

Is it possible to get more than one Row for a single key if there were multiple values with same key in original table?

Answer 1

This is a pivot type query and this is usually done using filtered aggregation:

select string_agg(value, ',') filter (where key = 'key1') as key_1,
       string_agg(value, ',') filter (where key = 'key2') as key_2,
       string_agg(value, ',') filter (where key = 'key3') as key_3
from the_table;

It's not possible to make this dynamic.

A fundamental restriction of SQL is that the number, names and types of all columns of a query must be known before the query is executed.

---

Depending on how you process the result, aggregating everything into a JSON object might be an alternative:

select jsonb_object_agg(key, vals)
from (
   select key, jsonb_agg(value) as vals
   from the_table
   group by key
) t
;