CODEWITHSUNDEEP
sqlpostgresql
user_101234
user_101234

Reputation: 1

How can I transform a column containing some strings into multi columns using postgresql?

I have the following table (the data type of the column value is varchar, some values such as c2 and c4 are missing) : 

__________________________
id |  value
__________________________
1  | {{a1,b1,c1},{a2,b2,}}
__________________________
2  | {{a3,b3,c3},{a4,b4}}
__________________________

and I would like to obtain something like:


id | A  |  B  |  C  
__________________
1  | a1 | b1  | c1
__________________
1  | a2 | b2  |   
__________________
2  | a3 | b3  | c3
__________________
2  | a4 | b4  |

I am trying to use regexp_split_to_array, without any success so far. How can this be achieved? Thank you!

Upvotes: 0

Views: 79

Answers (2)

Francesco Simeoli
Francesco Simeoli

Reputation: 541

You can use string_to_array to convert string to array and then explode it in multiple rows with unnest:

EXAMPLE

SELECT unnest(string_to_array('{1 2 3},{4 5 6},{7 8 9}', ','));


{1 2 3}
{4 5 6}
{7 8 9}

Upvotes: 0

404
404

Reputation: 8572

This assumes you know what the possible values are (e.g. a*, b*) because otherwise generating the appropriate columns for the value types will require dynamic sql.

Setup:

CREATE TABLE t (id INTEGER, value VARCHAR);

INSERT INTO t
VALUES 
  (1, '{{a1,b1,c1},{a2,b2,}}'),
  (2, '{{a3,b3,c3},{a4,b4}}')
;

Query:

SELECT
    id,
    NULLIF(r[1], '') AS a,
    NULLIF(r[2], '') AS b,
    NULLIF(r[3], '') AS c
FROM (
  SELECT id, regexp_split_to_array(r[1], ',') AS r
  FROM (
    SELECT id, regexp_matches(value, '{([^{][^}]+)}', 'g') AS r
    FROM t
  ) x
) x;

Result:

| id  | a   | b   | c   |
| --- | --- | --- | --- |
| 1   | a1  | b1  | c1  |
| 1   | a2  | b2  |     |
| 2   | a3  | b3  | c3  |
| 2   | a4  | b4  |     |

Note that if it's possible for earlier values to be missing, e.g. {b1,c1} where a1 is missing, then the query would have to be different.

Upvotes: 1

Related Questions