Create additional columns based on other column values in PostgreSQL

Question

I have following data in a PostgreSQL table:

trial   start_date  end_date            
1       20_12_2001  20_01_2005

The expected output is below:

trial   start_date  end_date    Date[(start_end_date)]  marker_start_end
1       20_12_2001  20_01_2005  20_12_2001              start
1       20_12_2001  20_01_2005  20_01_2005              end

Is there a way to calculate the additional two columns (Date[(start_end_date)], marker_start_end) without join, but a CASE expression

No, there is no way to do that, unless in combination with a UNION. A CASE statement will allow more complex conditions to produce columns for a row but it will not produce additional rows. — Patrick
– Patrick, Commented Jun 28, 2022 at 21:08

user330315 · Accepted Answer · 2022-06-28 20:47:12Z

2

You can use a lateral join to turn two columns into two rows:

select *
from the_table t
   cross join lateral (
     values (t.start_date, 'start'), (t.end_date, 'end')
   ) as x(start_end_date, marker);

The UNION ALL solution might be faster though.

answered Jun 28, 2022 at 20:47

user330315

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

4 Comments

Kadet Over a year ago

Nice solution. Union all is fastest, but where condition must be repeated twice, so not so friendly

Patrick Over a year ago

The OP asked for a solution with a CASE statement, specifically not a JOIN

Kadet Over a year ago

But to multiply rows it can be join with optional case or union without need of case. Sounds like mission impossible :)

Patrick Over a year ago

Sounds like a SQL course assignment to me!

Kadet · Accepted Answer · 2022-06-28 21:16:54Z

0

UNION ALL

select trial, start_date, end_date, start_date as date, 'start' marker_start_end from table1
union all
select trial, start_date, end_date, end_date as date, 'end' marker_start_end from table1

UNNEST with CASE

select trial, start_date, end_date, 
case when a.num = 1 then start_date else end_date end date, 
case when a.num = 1 then 'start' else 'end' end marker_start_end from
(
select trial, start_date, end_date, 
unnest(array[1,2]) num from table1
) a

Hidden JOIN (but still join)

select 
  trial, 
  start_date, 
  end_date, 
  case when a.num = 1 then start_date else end_date end date, 
  marker_start_end 
from table1, (values(1,'start'),(2, 'end')) a(num,marker_start_end)

Db fiddle

edited Jun 28, 2022 at 21:16

answered Jun 28, 2022 at 20:30

Kadet

1,4195 silver badges11 bronze badges

Collectives™ on Stack Overflow

Create additional columns based on other column values in PostgreSQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related