Convert varchar array to rows in redshift

MysticRenge 0
Viewed 40

Was able to do this in postgres using unnest function, but unable to replicate this on redshift since redshift does not have such a function.

Sample values are like below. The problem is that the number of elements in department are not fixed. max value is not known and can keep changing.Secondly the field department is stored as a varchar and not array in the DB.

name    department
abc   {admin,accounts}
xyz   {accounts}
mnp   {devices,accounts,finances}

Code so far, but giving an empty result

with emp_Data as (
select name, array(department) as dep from
table_emp
)

 select a.name,
         dep_unpivot
   from emp_Data as a,
  UNPIVOT a.dep AS dep_unpivot at name
amazon-redshift amazon-web-services sql
1 Answers

You can use the split_to_array function on the department column and then query the nested data

with emp_data as
(select name, split_to_array(department) as dep 
from table_emp)

select e.*, d
from emp_data e, e.dep d
MP240
Related