Snowflake: convert arrays to rows

In Snowflake we can have a column containing an array like this:

To convert that array column to string we just need to use Array_to_String like this:

SELECT Column1, Array_to_String(Column2, ',') AS Column2
FROM TABLE1;        

And voila, Column2 becomes string:

To convert the values in Column2 into rows, we just need to use Split_to_Table function, like this:

SELECT Column1, VALUE AS Column2
FROM TABLE1 T, LATERAL SPLIT_TO_TABLE(T.Column2, ',')        

?And voila, Column2 becomes rows:

Now, if we combine Array_to_String and Split_to_Table together, we can convert that arrow column in the beginning of this article to be the rows above, like this:

SELECT Column1, VALUE AS Column2
FROM TABLE1 T,
    LATERAL SPLIT_TO_TABLE(ARRAY_TO_STRING(T.Column2, ','), ',')        

And the output is the same as last time:

I hope this helps!

Andy Webster

Data Cloud Platform, Data Analytics Engineer, Certified DBA MSc, BSc

4 个月

Nice one Vincent.

要查看或添加评论,请登录

Vincent Rainardi的更多文章

社区洞察

其他会员也浏览了