unnest a column and merge rows based on the unnested column in Postgres

Question

I have a table like this:

ID	A's price	B's price	C's price	D's price
A,B,C	1	2	3	null
D	null	null	null	4
B	null	10	null	null

what I want to achieve:

ID	price
A	1
B	2,10
C	3
D	4

I tried unnest(string_to_array(ID,',')) then concat_ws(',', "A's price", "B's price", "C's price", "D's price"), it doesn't give me the desired output.

Is there a way to achieve this in Postgres or do I need to switch to pandas?

Appreciate any ideas and suggestions!

Answer 1

First thing - this data design is horrible. Think about normalization (at least 1NF) and then querying will be so much simpler.
Flatten the table in t subquery and then group by individual id and re-aggregate.

-- Test data
create table the_table 
(
 id text, 
 aprice numeric, 
 bprice numeric, 
 cprice numeric, 
 dprice numeric
);
insert into the_table values
('A,B,C', 1,    2,    3,    null),
('D',     null, null, null, 4),
('B',     null, 10,   null, null);

-- Query
select id,
  string_agg (case id 
     when 'A' then aprice 
     when 'B' then bprice 
     when 'C' then cprice
     when 'D' then dprice 
     end ::text, ',') as price
from
(
  select unnest(string_to_array(id, ',')) as id, aprice, bprice, cprice, dprice
  from the_table
) as t
group by id order by id;