I am using Presto. I want to assign a row with multiple ‘tags’ using different criteria that are not mutually exclusive.
For example, let’s say there’s a table with 4 columns:
| food | color | type | on_sale | |------------+--------+-------------+---------| | apple | red | fruit | TRUE | | banana | yellow | fruit | FALSE | | cornbread | yellow | baked goods | TRUE | | apple pie | brown | baked goods | TRUE |
I’d like to tag each food with tags that I’ve defined, such as
red food on sale,
yellow baked goods,
yellow food on_sale ONLY. In the example, apple matches two tags:
red food and
red food on sale.
I can’t use
CASE (like below) because I would only get back ‘red food’ when I actually want it labeled as both.
CASE WHEN color = 'red' THEN 'red food' WHEN color = 'red' AND `on_sale` = TRUE THEN 'red food on sale' (and so on)
Ideally, I want to return an array that can capture more than 1 ‘tag’ or duplicate columns with one line for each tag, but I have no idea how to do that. Any ideas?
Thanks in advance!
You can use string concatenation:
LTRIM(CASE WHEN color = 'red' THEN ' red food' ELSE '' END || CASE WHEN `on_sale` = TRUE THEN ' on sale' ELSE '' END END)