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?
CodePudding user response:
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;