Split comma-separated column data into multiple columns (Variable size)

Question

Need some help splitting column data (string) in Postgres into multiple dummy columns such as:

---------column-------
data1;data2;data3 -
data1;data3       -
data1             -

into

- Col1 ---- Col2 ---- Col3
   1    |    1    |   1
   1    |    0    |   1
   1    |    0    |   0

I know the maximum number of columns so I can preset the dummy columns. Do I need to do some sort of for loop?

JP

user330315 · Accepted Answer · 2018-07-03 21:19:09Z

3

You can either use split_part():

select split_part(col, ';', 1) as col1, 
       split_part(col, ';', 2) as col2, 
       split_part(col, ';', 3) as col1
from the_table;

Or slightly more efficient because the splitting is only done once per row:

select c[1] as col1, 
       c[2] as col2, 
       c[3] as col3
from (
  select string_to_array(col, ';') as c
  from the_table
) t;

answered Jul 3, 2018 at 21:19

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1