postrgresql分裂单列基于名称和值多列
问题描述:
我的表ABC只包含1列如下::postrgresql分裂单列基于名称和值多列
name=>countryvalue=>India name=>populationvalue=>10000000 name=>rankvalue=>25 name=>typevalue=>developing
name=>countryvalue=>USA name=>populationvalue=>100000 name=>rankvalue=>3 name=>typevalue=>developed
name=>countryvalue=>China name=>populationvalue=>15000000 name=>rankvalue=>5 name=>typevalue=>developed
我的预期成果是象下面这样:
country population rank type
India 10000000 25 developing
USA 100000 3 developed
China 15000000 5 developed
答
水木清华是怎样的?
f=# with tbl(cl) as (select 'name=>countryvalue=>India name=>populationvalue=>10000000 name=>rankvalue=>25 name=>typevalue=>developing'::text)
, part as (select split_part(cl,'name=>',2) a, split_part(cl,'name=>',3) b, split_part(cl,'name=>',4) c, split_part(cl,'name=>',5) d from tbl)
select split_part(a,'=>',2),split_part(b,'=>',2),split_part(c,'=>',2),split_part(d,'=>',2) from part;
split_part | split_part | split_part | split_part
------------+------------+------------+------------
India | 10000000 | 25 | developing
(1 row)
当然
如果列的顺序不批 - 你需要精心的分隔符
以上色狼,但巧妙的方法:
f=# create table rt(country text, population text, rank int, type text);
CREATE TABLE
f=# with hs as (select hstore_to_json(substr(replace(replace('name=>countryvalue=>India name=>populationvalue=>10000000 name=>rankvalue=>25 name=>typevalue=>developing',' name=>',','),'value=>','=>'),7)::hstore) a)
select j.* from hs, json_populate_record(null::rt, a) j;
country | population | rank | type
---------+------------+------+------------
India | 10000000 | 25 | developing
(1 row)
为此,你需要hstore延伸,类型准备(或表格)和热情
列值由列名和值组成? – SachinSarawgi
'split_part'?.. –
@SachinSarawgi yes。它包含列名和值 – DSawant