How to get a proper name from the dictionary table in sql-query

user_14463139 0
Viewed 33

I got stuck in the next situation. I have two tables, table "Data" and table "Dictionary".

Each row of the table "Data" is info about product. There are exist 10 columns not including others in this row, which names "cat1", "cat2" ... "cat10". These columns could contain INT value or NULL value.

"Data" table looks like:

product_id product_name price cat1 cat2 ... cat10
13 banana 3.5 12 32 ... NULL

"Dictionary" table contains proper name of each categories:

cat_id cat_name
12 fruits
32 eat

My main goal is writing a query which will be look like these:

product_name categories
banana fruits,eat

I tried to use CONCAT(), but if we have NULL values it doesn't work. Could anyone help me to create SQL statement to get it?

snowflake-cloud-data-platform sql
1 Answers

First the columns cat1..cat10 should be unpivoted to multiple rows, then joined to Dictionary table and finally the result aggregated using LISTAGG:

WITH cte AS (
  SELECT *
  FROM Data d
  UNPIVOT (cat_id for category IN (cat1, cat2, ... cat10)
)
SELECT c.product_id, c.product_name, LISTAGG(d.cat_name, ',') AS categories
FROM cte c
JOIN "Dictionary" d
  ON c.cat_id = d.cat_id
GROUP BY c.product_id, c.product_name;
user_50708790
Related