CODEWITHSUNDEEP
sqlpostgresqlsplit
Garfield
Garfield

Reputation: 143

Postgres: Split column values & transpose

I have a table like this:

category_id | product_id
---------------------------
1           | 1;2;3;4
2           | 7;8;9
3           | 6

And I want to convert it to this:

category_id | product_id
---------------------------
1           | 1
1           | 2
1           | 3
1           | 4
2           | 7
2           | 8
2           | 9
3           | 6

I have tried splitting the column based on ; but the product_id count varies based on the category_id. Is there anyway I can achieve this?

Upvotes: 1

Views: 2544

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Postgres has a function regexp_split_to_table() that does exactly what you want -- split a string into multiple rows I prefer to be explicit about my lateral joins, so I would write this as:

select t.category_id, r.product_id
from t cross join lateral
     regexp_split_to_table(t.product_id, ';') r(product_id);

However, if you prefer conciseness, you can write this as:

select t.category_id, regexp_split_to_table(t.product_id, ';') as product_id
from t;

Here is a db<>fiddle.

Upvotes: 1

Amadan
Amadan

Reputation: 198446

You can use PostgreSQL's array manipulation functions:

SELECT category_id, unnest(string_to_array(product_id, ';')) FROM test;

string_to_array does exactly what it says — splits a string into an array of parts using a supplied delimiter, then unnest is used to separate an array value into multiple rows containing elements from the array.

Upvotes: 3

Related Questions