I have a table like this - I want to parse each row of the message column and create a new table with the following format by taking each distinct 2-digit prefix of the value and setting that as a new column. How do I write this in snowflake?
How to parse comma separated values in one column and convert each distinct value as a column for each record using snowflake?
Robl09
0
Viewed 23
1 Answers
Using SPLIT_TO_TABLE and conditional aggregation:
SELECT RowID,
MAX(CASE WHEN s.value ILIKE 'V22%' THEN s.value END) AS V22,
MAX(CASE WHEN s.value ILIKE 'V23%' THEN s.value END) AS V23,
MAX(CASE WHEN s.value ILIKE 'V24%' THEN s.value END) AS V24
FROM tab, LATERAL SPLIT_TO_TABLE(tab.Message, ',') AS s
GROUP BY RowID;
Output:
Related