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.

Improve this question
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? Commented Jan 20, 2018 at 18:52

1 Answer 1

Reset to default
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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.