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.

  • 3
    If you want the number of columns returned to be variable, you would need to dynamically build the query at runtime (i.e. dynamic SQL in a stored procedure). If you want a fixed number N of product columns, you can build a static pivot query. In a data warehouse environment, though, you would normally let your BI tool handle pivoting the data (particularly dynamically) rather than hand coding those sorts of queries – Justin Cave Jan 19 '18 at 21:52
  • 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 '18 at 18:52

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
       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|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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