PostgreSQL 9.3: convert column to row

Question

I would like to create a view from a table like this :

Example in table :

cola      column1        
-------------------
D1        val1, val2
D3        val3

Expected Result into a view:

cola      column1        
-------------------
D1        val1
D1        val2
D3        val3

I want to divide the value of column1 and create a new row, but keep the values of the other columns.

You could set up three queries (single values, left value, right value) and combine them with a `UNION`. Or maybe look at [https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns]. — Renardo, Mar 14 '18 at 09:32

score 2 · Answer 1 · answered Mar 14 '18 at 09:34

2

just agg to array and unnest it:

t=# with c(cola,c1) as (values('d1','v1,v2'),('d2','v3'))
select cola,unnest(string_to_array(c1,',')) column1 from c;
 cola | column1
------+---------
 d1   | v1
 d1   | v2
 d2   | v3
(3 rows)

from :

t=# with c(cola,c1) as (values('d1','v1,v2'),('d2','v3'))
select * from c;
 cola |  c1
------+-------
 d1   | v1,v2
 d2   | v3
(2 rows)

answered Mar 14 '18 at 09:34

Vao Tsun

47,234
13
100
132

Thanks, it's perfect :) – user2998243 Mar 14 '18 at 10:13

PostgreSQL 9.3: convert column to row

1 Answers1