Keboola WIKI‎ > ‎Transformations‎ > ‎Snowflake‎ > ‎

How to parse column into rows

by martin.matejka@keboola.com

This is something you often need to do when dealing with some type of categories/events/states etc. and the values are presented in a single column divided by some separator.
Imagine table like this one (id of the product and column with categories the product belongs to, separated by pipe):
You don't necessary know how many categories there could be, it differs for some types of products. Probably you want to have a table, where each product has all of its categories on different rows, so you're able to do more with it. Before you'll be begging some R/Python guy/girl to parse it for you, you can try to use this short SQL script:
SELECT
"product_id" AS "object_id",
trim("c".value :: VARCHAR(100)) AS "category"
FROM "categories",
LATERAL flatten(input => split("categories", '|')) "c";
Voila:
Well, that was quick, right?

Enjoy!
Comments