Create additional columns based on other column values in PostgreSQL-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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