Split array of values into rows in Amazon Redshift

Question

How can i split an array of values in a column into corresponding rows in Redshift using a delimiter (,) ?

Input Data:-

—————————————  
Empid | Items  
—————————————  
1001| A, B  
1002| B  
1003| C, D, E  

Required Output:-

—————————————  
Empid | Items  
—————————————  
1001| A  
1001| B  
1002| B  
1003| C  
1003| D  
1003| E  

Any help is appreciated.

Thanks

Answer 1

Based on the official docs, you can do with JOIN!

Let's say your input is:

—————————————  
empid | items  
—————————————  
1001| [A, B]  
1002| [B]  
1003| [C, D, E]
1004| []

Then you can do it as:

SELECT t.empid, items as item
FROM table_name AS t
    LEFT JOIN t.items AS items ON TRUE

This will returns:

—————————————  
empid | item 
—————————————  
1001| A  
1001| B  
1002| B  
1003| C  
1003| D  
1003| E
1004| <NULL>