PatternSQL
Split a delimited column into rows
Turn a comma-separated tags column into one row per tag with DuckDB's unnest(string_split(...)) — the SQL equivalent of exploding a list.
Last updated
Pattern
SELECT id, unnest(string_split(tags, ',')) AS tag FROM products;
Why it’s written this way
string_split(tags, ',') breaks the delimited text into a list value first — DuckDB has a genuine list type, so at that point the row still holds a single list column. unnest() is what turns that list back into multiple rows, one per element, repeating the row's other columns (id) alongside each one.
The two functions compose in a single SELECT because DuckDB allows unnest() to appear directly in the SELECT list next to ordinary columns, with no LATERAL join or separate CROSS JOIN UNNEST(...) clause required.
Edge cases to know
- →This exact syntax is DuckDB-specific. Postgres needs unnest(string_to_array(tags, ',')); Snowflake and BigQuery reach for SPLIT combined with a lateral FLATTEN or UNNEST — check the target engine before copying this verbatim.
- →An empty tags value produces one empty-string element rather than zero rows — filter with WHERE tag <> '' afterward if a blank shouldn't count as a tag.
- →Whitespace around the delimiter ('a, b, c') ends up inside each element ('a', ' b', ' c'); wrap with trim(unnest(...)) if the source data isn't already clean.