How to change "65→67→69" to "J7,G2,P9" in SQL/PostgreSQL/MySQL? Or use split fields/value mapper in Pentaho Data Integration (Spoon) to realize it?
I use KETTLE(Pentaho Data Integration/Spoon) to insert data to PostgreSQL from other databases, I have a field with below data
value
-----------
65→67→69
15→19→17
25→23→45
19→28→98
ID value
--------
65 J7
67 G2
69 P9
15 A8
19 b9
17 C1
25 b12
23 e12
45 A23
28 C17
98 F18
And how to change the above value to the below value? Is there any SQL way or KETTLE way to realize it?
new_value
-----------
J7,G2,P9
A8,b9,C1
b12,e12,A23
b9,C17,B18
Thanks so much for any advice.
Assuming these tables:
create table table1 (value text);
insert into table1 (value)
values
('65→67→69'),
('15→19→17'),
('25→23→45'),
('19→28→98')
;
create table table2 (id int, value text);
insert into table2 (id, value)
values
(65, 'J7'),
(67, 'G2'),
(69, 'P9'),
(15, 'A8'),
(19, 'b9'),
(17, 'C1'),
(25, 'b12'),
(23, 'e12'),
(45, 'A23'),
(28, 'C17'),
(98, 'F18')
;
In Postgres you can use a scalar subselect:
select t1.value,
(select string_agg(t2.value, ',' order by t.idx)
from table_2 t2
join lateral unnest(string_to_array(t1.value,'→')) with ordinality as t(val,idx) on t2.id::text = t.val
) as new_value
from table_1 t1;