Postgres turn join rows into columns

Question

I have this database schema:

drop table if exists demo_contact cascade;
create table demo_contact (id serial primary key);
insert into demo_contact values (1);
insert into demo_contact values (2);
insert into demo_contact values (3);

drop table if exists demo_contact_custom_field cascade;
create table demo_contact_custom_field (custom_field_id text, contact_id numeric, value text);
insert into demo_contact_custom_field values ('7759512f-662f-4139-94fb-8b708c5d11eb', 1, '3232');
insert into demo_contact_custom_field values ('a96993bf-eb38-446c-a5a7-416485e8b933', 1, 'true');
insert into demo_contact_custom_field values ('a96993bf-eb38-446c-a5a7-416485e8b933', 2, 'true');

How can I produce this sort of output?

contact_id	7759512f-662f-4139-94fb-8b708c5d11eb	a96993bf-eb38-446c-a5a7-416485e8b933
1	3232	true
2		true

I searched around for various queries relating to transposing a table, pivot tables in Postgres, this literal question title "turn postgres join rows into columns" but I haven't found a solution:

• https://dba.stackexchange.com/questions/246508/sql-join-to-put-rows-into-columns This seems to have an incorrect answer
• Concatenate multiple result rows of one column into one, group by another column This is the second Google hit but not what I need (I don't need an array column, I need a column for each joined table row)
• https://dirask.com/posts/PostgreSQL-concatenate-multiple-rows-into-one-field-DLok61 Again not what I need, I don't want to join multiple values into a single cell
• https://www.quora.com/How-do-you-combine-multiple-rows-into-multiple-columns-with-PostgreSQL-SQL-PostgreSQL-development Recommends looking up Postgres pivot table and crosstab but from the look I had on that, this seems to be related to aggregating values to cells?

Answer 1

This is typically done using filtered aggregation:

select contact_id, 
        max(value) filter (where custom_field_id = 'a96993bf-eb38-446c-a5a7-416485e8b933') as "a96993bf-eb38-446c-a5a7-416485e8b933",
        max(value) filter (where custom_field_id = '7759512f-662f-4139-94fb-8b708c5d11eb') as "7759512f-662f-4139-94fb-8b708c5d11eb"
from demo_contact_custom_field 
group by contact_id
order by contact_id;

Online example

A fundamental restriction of the SQL language is that, the number, names and data types of all columns of a query must be known to the database before it retrieves the result of the query. You can not have a query that returns 2 columns today and 42 tomorrow without any change to the query itself.

---

A workaround is to aggregate into JSON values as shown in histocrat's answer. Or create a view with all possible columns based on the data. See e.g. this answer for an example

Answer 2

I don't think it's possible to do this exact thing with PostgreSQL. Columns are assumed to be static per query, so they can't be built dynamically (as far as I know). You can do something very similar, though:

SELECT contact_id, json_object_agg(custom_field_id,value)
FROM demo_contact_custom_field 
GROUP BY contact_id;

That will get you output that looks like

contact_id	json_object_agg
1	{"7759512f-662f-4139-94fb-8b708c5d11eb":"3232","a96993bf-eb38-446c-a5a7-416485e8b933":"true"}
2	{"a96993bf-eb38-446c-a5a7-416485e8b933":"true"}

---

View on DB Fiddle

You could also take the approach described here of programmatically building a return type or query based on the rows of the table.

Answer 3

You can also consider crosstab function https://www.postgresql.org/docs/current/tablefunc.html#id-1.11.7.52.5

further this would help - demonstration of crosstab: https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/

below is not your answer, but it is basic implementation

SELECT *
FROM   crosstab(
       'select custom_field_id, contact_id, value from demo_contact_custom_field a inner join demo_contact b on a.contact_id=b.id'
   ) t (col text, r1 text,r2 text);