Dynamic transpose for unknown row value into column name on postgres

Question

I have table such:

customer_number	label	value
1	address	St. John 1A
1	phone	111111111
1	email	[email protected]
2	address	St. Marry 231A
2	phone	222222222
2	email	[email protected]

I want new table or view so it's become:

customer_number	address	phone	email
1	St. John 1A	111111111	[email protected]
2	St. Marry 231A	222222222	[email protected]

but in the future there are possibility to add different label, for example there might be new label called occupation.

Important to note, I don't know the value of the label column, so it's should iterate to any value inside that column.

Is there any way to do this?

score 1 · Accepted Answer · 2021-03-22 10:22:35Z

1

You can't have a "dynamic" pivot as the number, names and data types of all columns of a query must be known to the database before the query is actually executed (i.e. at parse time).

I find aggregating stuff into a JSON easier to deal with.

select customer_number,
       jsonb_object_agg(label, value) as props
from the_table
group by customer_number

If your frontend can deal with JSON values directly, you can stop here.

If you really need a view with one column per attribute, you can them from the JSON value:

select customer_number, 
       props ->> 'address' as address,
       props ->> 'phone' as phone,
       props ->> 'email' as email
from (       
  select customer_number,
         jsonb_object_agg(label, value) as props
  from the_table
  group by customer_number
) t

I find this a bit easier to manage when new attributes are added.

If you need a view with all labels, you can create a stored procedure to dynamically create it. If the number of different labels doesn't change too often, this might be a solution:

create procedure create_customer_view() 
as
$$
declare
  l_sql text;
  l_columns text;
begin
  select string_agg(distinct format('(props ->> %L) as %I', label, label), ', ')
    into l_columns
  from the_table;
  
  l_sql := 
    'create view customer_properties as 
     select customer_number, '||l_columns||' 
     from (
      select customer_number, jsonb_object_agg(label, value) as props
       from the_table 
       group by customer_number 
     ) t';
  execute l_sql;
end;
$$
language plpgsql;

Then create the view using:

call create_customer_view();

And in your code just use:

select *
from customer_properties;

You can schedule that procedure to run in regular intervals (e.g. through a cron job on Linux)

edited Mar 22, 2021 at 10:22

answered Mar 22, 2021 at 10:12

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

F. Suyuti Over a year ago

So I must know every possible label value?

user330315 Over a year ago

@F.Suyuti: yes, but you can create a procedure to dynamically create a view with the current labels. See my edit

F. Suyuti Over a year ago

your procedure works like a charm, thanks

Tom Mac · Accepted Answer · 2021-03-22 10:07:38Z

Generally-speaking SQL is not good at pivotting dynamically.

Here is a query that will pivot the data for you. However, it is not dynamic i.e. if a future occupation label was added then you would have to change the query. Not sure whether that is acceptable or not :

select customer_number,
max(value) filter (where label='address') as address,
max(value) filter (where label='phone') as phone,
max(value) filter (where label='email') as email
from your_customer_table
group by customer_number

Bit of an assumption that you are running Postgres 9.4 or better here so that the filter function is supported. If not then it can be re-worked using case statements :

select customer_number,
max(case when label='address' then value else null end) as address,
max(case when label='phone' then value else null end) as phone,
max(case when label='email' then value else null end) as email
from your_customer_table
group by customer_number

Khoa Hoàng · Accepted Answer · 2021-03-22 10:06:16Z

-1

I used cross apply to solve this problem .. Here is my query

select distinct tb9.customer_number, tb9_2.*
from Table_9 tb9 cross apply
     (select max(case when tb9_2.[label] like '%address%' then [value] end) as [address],
             max(case when tb9_2.[label] like '%phone%' then [value] end) as [phone],
             max(case when tb9_2.[label] like '%email%' then [value] end) as [email]
      from Table_9 tb9_2
      where tb9.customer_number = tb9_2.customer_number
     ) tb9_2;

answered Mar 22, 2021 at 10:06

Khoa Hoàng

841 silver badge7 bronze badges

3 Comments

user330315 Over a year ago

That's not valid for Postgres (or standard SQL)

Khoa Hoàng Over a year ago

I don't pay attention to Postgres, but it works on MS SQL @a_horse_with_no_name , it could be the second way after the answer above. Thank u

user330315 Over a year ago

For Postgres you need to replace the non-standard cross apply with the standard compliant cross join lateral and you need to remove the non-standard square brackets around the column names.

Collectives™ on Stack Overflow

Dynamic transpose for unknown row value into column name on postgres

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related