Split column to multiple columns in PostgreSQL-CodePudding

I am going to split one column of my table to multiple columns. The fields are separated by comma and I used the split_part. The problem that I faced is the length of string are not same and after splitting they are located from column 1 to column N.

Column

a,b,c,d,e,f

b,c,e,f

c,d,e,f

my output is:

col1   col2  col3 col4 col5 col6

 a      b     c    d    e   f

 b      c     e    f

 c      d     e    f

but I want to have an output like this

 col1   col2  col3 col4 col5 col6


  a      b     c    d    e   f

         b     c         e   f

               c    d    e   f

CodePudding user response：

Given this table:

CREATE TABLE split_column (
    id integer PRIMARY KEY,
    col text NOT NULL
)

And this data:

INSERT INTO split_column (id, col) VALUES
    (1, 'a,b,c,d,e,f'),
    (2, 'b,c,e,f'),
    (3, 'c,d,e,f')

The first row contains all values:

SELECT string_to_array(col, ',') AS values
FROM split_column
ORDER BY id
LIMIT 1

values
{a,b,c,d,e,f}

We use it to create a mapping from col1..colN to the values:

WITH complete AS (
    SELECT string_to_array(col, ',') AS values
    FROM split_column
    ORDER BY id
    LIMIT 1
)
SELECT 'col' || value.ordinality AS key, value
FROM complete, unnest(complete.values) WITH ORDINALITY AS value

key	value
col1	a
col2	b
col3	c
col4	d
col5	e
col6	f

We aggregate the rows to JSON objects:

WITH complete AS (
    SELECT string_to_array(col, ',') AS values
    FROM split_column
    ORDER BY id
    LIMIT 1
), map AS (
    SELECT 'col' || value.ordinality AS key, value
    FROM complete, unnest(complete.values) WITH ORDINALITY AS value
)
SELECT id, jsonb_object_agg(map.key, map.value) AS object
FROM split_column, string_to_table(col, ',') AS col
JOIN map
    ON col = map.value
GROUP BY id
ORDER BY id

id	object
1	{"col1": "a", "col2": "b", "col3": "c", "col4": "d", "col5": "e", "col6": "f"}
2	{"col2": "b", "col3": "c", "col5": "e", "col6": "f"}
3	{"col3": "c", "col4": "d", "col5": "e", "col6": "f"}

And then we "destructure" them back into records:

WITH complete AS (
    SELECT string_to_array(col, ',') AS values
    FROM split_column
    ORDER BY id
    LIMIT 1
), map AS (
    SELECT 'col' || value.ordinality AS key, value
    FROM complete, unnest(complete.values) WITH ORDINALITY AS value
), agg AS (
    SELECT id, jsonb_object_agg(map.key, map.value) AS object
    FROM split_column, string_to_table(col, ',') AS col
    JOIN map
        ON col = map.value
    GROUP BY id
)
SELECT agg.id, cols.*
FROM agg, jsonb_to_record(agg.object) AS cols(col1 text, col2 text, col3 text, col4 text, col5 text, col6 text)
ORDER BY id

id	col1	col2	col3	col4	col5	col6
1	a	b	c	d	e	f
2		b	c		e	f
3			c	d	e	f

db<>fiddle

CodePudding user response：

Table and data def shamelessly stolen from @xehpuk

CREATE TABLE split_column (
    id integer PRIMARY KEY,
    col text NOT NULL
)

data

INSERT INTO split_column (id, col) VALUES
    (1, 'a,b,c,d,e,f'),
    (2, 'b,c,e,f'),
    (3, 'c,d,e,f')

Use Case statements in query:

select case when position('a' in col)::bool then 'a' end as col1,
       case when position('b' in col)::bool then 'b' end as col2,
       case when position('c' in col)::bool then 'c' end as col3,
       case when position('d' in col)::bool then 'd' end as col4,
       case when position('e' in col)::bool then 'e' end as col5,
       case when position('f' in col)::bool then 'f' end as col6
  from aaa.split_column

col1	col2	col3	col4	col5	col6
a	b	c	d	e	f
null	b	c	null	e	f
null	null	c	d	e	f