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	john@cena.com
2	address	St. Marry 231A
2	phone	222222222
2	email	please@marry.me

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

customer_number	address	phone	email
1	St. John 1A	111111111	john@cena.com
2	St. Marry 231A	222222222	please@marry.me

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?

Answer 1

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

Answer 2

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)

Answer 3

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;