How to get multiple comma-separated values as individual columns?

Question

I have a table with data like this:

select * from data

id |  col1 |  col2 |  col3
---+-------+-------+-------
 1 | 1,2,3 | 4,5,6 | 7,8,9

I want to get the data like this:

id | name | dd | fn | suf
---+------+----+----+-----
1  | col1 |  1 |  2 |  3
1  | col2 |  4 |  5 |  6
1  | col3 |  7 |  8 |  9

Currently, I use split_part() in a query like this:

SELECT * from(
select id,
       'col1' as name,
       NULLIF(split_part(col1, ',', 1), '') AS dd, 
       NULLIF(split_part(col1, ',', 2), '') AS fn, 
       NULLIF(split_part(col1, ',', 3), '') AS suf
       from data

       UNION 
       select id,
       'col2' as name,
       NULLIF(split_part(col2, ',', 1), '') AS dd, 
       NULLIF(split_part(col2, ',', 2), '') AS fn, 
       NULLIF(split_part(col2, ',', 3), '') AS suf
       from data
        UNION 
       select id,
       'col3' as name,
       NULLIF(split_part(col3, ',', 1), '') AS dd, 
       NULLIF(split_part(col3, ',', 2), '') AS fn, 
       NULLIF(split_part(col3, ',', 3), '') AS suf
       from data
);

Is there a more elegant way? I have 20 columns.

Your table definition and Postgres version would help. Data type of `col1`, `col2`, `col3` is `text`? or `int[]`? Elements are integer values? A maximum of 3 elements? Can there be 0 elements (empty string / array)? Desired result for that? Missing elements shall produce a NULL value? — Erwin Brandstetter, Dec 21 '16 at 13:18

score 1 · Accepted Answer · edited May 23 '17 at 10:30

1

Assuming this table:

CREATE TABLE tbl (id int, col1 text, col2 text, col3 text);
INSERT INTO tbl VALUES (1 ,'1,2,3', '4,5,6', '7,8,9');

A VALUES expression in a LATERAL subquery should be an elegant solution.
Then just use split_part(). Add NULLIF() only if there can be actual empty strings in the source ...

SELECT id, x.name
     , split_part(x.col, ',', 1) AS dd
     , split_part(x.col, ',', 2) AS fn
     , split_part(x.col, ',', 3) AS suf
FROM   tbl t, LATERAL (
   VALUES (text 'col1', t.col1)
        , (     'col2', t.col2)
        , (     'col3', t.col3)
        -- ... many more?
   ) x(name, col);

Works in PostgreSQL 9.3 or later.
SQL Fiddle.

Community

1
1

answered Dec 21 '16 at 13:28

Erwin Brandstetter

605,456
145
1,078
1,228

where name column ? – P S M Dec 21 '16 at 13:43
@PSM: I added the column `name`. – Erwin Brandstetter Dec 21 '16 at 13:53
Thanks.You saved my time. – P S M Dec 21 '16 at 14:36

score 0 · Answer 2 · answered Dec 21 '16 at 12:45

I would do the union all first and the split_part() second:

select id, name,
       coalesce(split_part(col, ',', 1), '') as dd,
       coalesce(split_part(col, ',', 2), '') as fn,
       coalesce(split_part(col, ',', 3), '') as suf
from ((select id, 'col1' as name, col1 as col from data
      ) union all
      (select id, 'col2' as name, col2 as col from data
      ) union all
      (select id, 'col3' as name, col3 as col from data
      )
     ) t;

How to get multiple comma-separated values as individual columns?

2 Answers2