Table has 2 array columns Key and Value. For EG:
| ID | Key | Value |
|---|---|---|
| 1 | a,b,c,d | 1,2,3,4 |
| 2 | b,c | 7,8 |
| 3 | a,c | 0,9 |
| 4 | b,c,d | 3,7,1 |
Need to map the Key to the Value and split it into separate columns
| ID | a | b | c | d |
|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 |
| 2 | 7 | 8 | ||
| 3 | 0 | 9 | ||
| 4 | 3 | 7 | 1 |
Add a comment
|
1 Answer
You can convert both string into arrays, unnest the arrays and aggregate those key/value pairs back into a JSON value. That value then can be used to extract the four keys again:
select id,
cols ->> 'a' as a,
cols ->> 'b' as b,
cols ->> 'c' as c,
cols ->> 'd' as d
from (
select id,
(select jsonb_object_agg(k,v)
from unnest(string_to_array(key,','),
string_to_array(value, ',')) as x(k,v)) as cols
from the_table
) x
order by id;
I would probably create a function to make the code a bit easier to read.