How to split or divide columns with multiple values separated by a comma into individual rows

Question

Current Result

ID	Orders
257	10007,10135,10136
249	10026,10137,10326
251	10029
3293	100502,100551

I would like to convert these multiple orders into individual rows in POSTGRES

Required Result

ID	Orders
257	10007
257	10135
257	10136
249	10026
249	10137
249	10326
251	10029
3293	100502
3293	100551

Answer 1

Solution for you problem:

SELECT ID,UNNEST(STRING_TO_ARRAY(Orders, ',')) Orders
FROM Table1;
 

db fiddle link

Unnest: It is an array function which expands an array into set of rows.

Syntax:

unnest ( anyarray )    

string_to_array: It is also an array function which splits string into array elements using supplied delimiter and optional null-string.

Here optional null-string parameter is used to replace the sub-strings in the input which matches the null-string parameter value with NULL value in the output or final array.

Syntax:

string_to_array(text, text [, text])

For more information on unnest and string_to_array syntax follow the below link:

https://www.postgresql.org/docs/14/functions-array.html

https://www.postgresql.org/docs/9.1/functions-array.html

Answer 2

Convert comma-separated text to array and use unnest function:

SELECT id, unnest(string_to_array[orders], ',')) FROM your_table;