Creating dynamic columns from table data

Question

Table data sample:

--------------------------
| key | domain | value   |
--------------------------
| a   | en     | English |
| a   | de     | Germany |

Query which returns result I need:

select * from 
(
    select t1.key,
        (select value from TABLE where t1.key=key AND code='en') en,
        (select value from TABLE where t1.key=key AND code='de') de
    from TABLE t1
) as t2

Data returned from query:

---------------------------
| key | en      | de      |
---------------------------
| a   | English | Germany |

I do not want to list all available domains with:

(select value from TABLE where t1.key=key AND code='*') *

Is it possible to make this query more dynamic in Postgres: automatically add all domain columns that exist in the table?

So you want all distinct non-null domain values as column names? You'll probably have to use dynamic sql in plpgsql and execute it with EXECUTE. — 404, Apr 24 '18 at 14:09
Dynamic number of columns? Plain SQL cannot do that, but pl/pgSQL could, as @eurotrash says. — The Impaler, Apr 24 '18 at 14:23
[Dynamic pivot query](https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3#28149684) — Lukasz Szozda, Apr 24 '18 at 14:37

score 0 · Accepted Answer · answered Apr 24 '18 at 15:23

For more than a few domains use crosstab() to make the query shorter and faster.

PostgreSQL Crosstab Query

A completely dynamic query, returning a dynamic number of columns based on data in your table is not possible, because SQL is strictly typed. Whatever you try, you'll end up needing two steps. Step 1: generate the query, step 2: execute it.

Execute a dynamic crosstab query

Or you return something more flexible instead of table columns, like an array or a document type like json. Details:

Creating dynamic columns from table data

1 Answers1