Postgres: Split column values & transpose

Question

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?

Does this answer your question? [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — TimLer, Aug 02 '21 at 06:56
@Rezu It does not — OP wants to split into rows, not columns. It is also about [tag:sql-server], not about [tag:postgresql]. — Amadan, Aug 02 '21 at 06:58

score 3 · Accepted Answer · answered Aug 02 '21 at 06:59

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.

score 1 · Answer 2 · answered Aug 02 '21 at 11:41

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.

Postgres: Split column values & transpose

2 Answers2