当run生成此表时,我有以下sql
select id, trim(s.my_data) as my_data from my_table t, unnest(string_to_array(t.column_to_parse, ':')) s(my_data)
id | my_data
------------
1 | first
1 | last=bbb
1 | middle=ccc
1 | bottle=ddd
现在,我想使用my_data的内容创建一个表,并拆分my_data列,使“=”之前的第一部分成为列名
例如
id | first | last | middle | bottle
-------------------------------------
1 | | bbb | ccc | ddd
我不能硬编码这些值,它们每次都会不同,我需要从表的列值创建列名
我该怎么做?
最佳答案
SELECT id,
split_part(parsed[1], '=', 2) AS first,
split_part(parsed[2], '=', 2) AS last,
split_part(parsed[3], '=', 2) AS middle,
split_part(parsed[4], '=', 2) AS bottle
FROM (SELECT id,
string_to_array(column_to_parse, ':') parsed
FROM my_table) q;
id | first | last | middle | bottle
----+-------+------+--------+--------
1 | | bbb | ccc | ddd
(1 row)