How to split row into two rows in PostgreSQL?

Question

There is a data_table with four columns. I am trying to make up a SELECT statement that transforms each initial row into one or two rows depending on a value in type column.

For example, when type = 'X' row (a,b,c) is transformed into (a,b) and (a,c), otherwise it is just (a,b).

initial_data_table

|  a   |  b  |  c  |  type
---------------------------
|  1   |  2  |  3  |   X  
|  4   |  5  |  6  |   Y
---------------------------

query result

|  first   | second |  type
---------------------------
|    1     |   2    |   X  
|    1     |   3    |   X  
|    4     |   5    |   Y
---------------------------

Could you help me tow find out how to "split" rows on condition in a relational way using only SQL?

Gordon Linoff · Accepted Answer · 2021-06-29 01:36:44Z

1

I am thinking a lateral join with some filtering:

select v.*
from t cross join lateral
     (values (a, b, type),
             (a, c, nullif(type, 'Y'))
     ) v(first, second, type)
where type is not null;

However union all would work well too:

select a, b, type
from t
union all
select a, b, type
from t
where type = 'X';

answered Jun 29, 2021 at 1:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to split row into two rows in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related