How create a column containing columns as values in redshift?

0
votes

I have a data like this below :

id                  start_date   end_date   col1   col2   col3   col4   col5
issue_2017-09       2017-09-18  2017-09-30  true   true   true   true   false

i want to convert data into the following format:

id                 start_date    end_date     new_col
issue_2017-09      2017-09-18    2017-09-30   {'col1', 'col2', 'col3', 'col4'}

new_col is created out of the columns [col1, col2, col3, col4, col5] which are true. plus I am using redshift.

2
amazon-web-servicesamazon-redshift

2 Answers

1
votes

I was able to resolve this using the following query :

select id, start_date , end_date, listagg(col_name, ', ') as new_col
from (
   select id, start_date, end_date, col1 as val, 'col1' as col_name
   from table
   union all
   select id, start_date, end_date, col2 as val, 'col2' as col_name
   from table
   union all
   select id, start_date, end_date, col3 as val, 'col3' as col_name
   from table
   union all
   select id, start_date, end_date, col4 as val, 'col4' as col_name
   from table
   union all
  select id, start_date, end_date, col5 as val, 'col5' as col_name
   from table
) t
where val is True
group by id, start_date, end_date
1
votes

Here is an alternative method,

select
  id, start_date, end_date, 
  '{' + 
  case when col1 then '''col1''' else '' end +
  case when col2 then case when col1 then ', ''col2''' else '''col2''' end else '' end +
  case when col3 then case when (col1 or col2) then ', ''col3''' else '''col3''' end else '' end +
  case when col4 then case when (col1 or col2 or col3) then ', ''col4''' else '''col4''' end else '' end +
  case when col5 then case when (col1 or col2 or col3 or col4) then ', ''col5''' else '''col5''' end else '' end +
  '}' as new_col 
 from table01;