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

Time:06-22

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;
  • Related