Question 90093 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres turn join rows into columns – Postgresql

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:

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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);

    enter image description here

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search