CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
MysticRenge
MysticRenge

Reputation: 385

Convert varchar array to rows in redshift

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

Upvotes: 0

Views: 3410

Answers (1)

MP24
MP24

Reputation: 3200

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

Upvotes: 2

Related Questions