0

I have a data warehouse with a One-To-Many relationship:

"Fact" table contains: Product_Group_ID
"Product_Bridge" table contains: Product_Group_ID, Product_ID
"Product" table contains: Product_ID, Product_Name, other product attributes..

I've inserted the data into the relevant tables, but how can I query the database and retrieve all products from a product group in the one row result? I've never used a bridge table before and could not find any decent/helpful examples online for how to actually query between the bridge/fact/dimensions.

i.e. the desired result would look like:

product_group_ID product_ID product_name product_ID_1 product_name1 product_ID_n product_name_n

Any suggestions would be greatly appreciated.

1
  • you want to retrieve one row for a each product_group_ID, and a column of this row contains a string that is the concatenation of the values product_group_ID, product_ID, product_name, product_ID_1, product_name1, ..., product_ID_ and product_name_n, Is this true?
    – miracle173
    Jan 20, 2018 at 18:52

1 Answer 1

1

Based on your example output you can use LISTAGG. The basic query that you can add whatever other criteria to is;

SELECT DISTINCT (p.product_group_id ||' '|| LISTAGG(c.product_id||'  '||c.product_name, ' ')
                                   WITHIN GROUP(ORDER BY p.product_group_id, c.product_id)
                                   OVER (PARTITION BY p.product_group_id))
  FROM product c
       INNER JOIN
       product_bride p ON c.product_id = p.product_id

If you need more attributes from the product then you will need to concatenate them in the LISTAGG. I haven't included a join to the Fact table but that can easily be included if you need something from it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.