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!
Data Cloud Platform, Data Analytics Engineer, Certified DBA MSc, BSc
4 个月Nice one Vincent.