Derek Hansen Derek Hansen - 7 months ago 17
SQL Question

SQL Server Concatenate using FOR XML dilemma

I come from an oracle background so in doing searches on this site I have found countless examples on how to use the FOR XML PATH to try to duplicate what LISTAGG() will do in oracle. However I don't know if what I am trying to do is outside of that scope or I am not figuring out what piece I am missing. Every example I have found just uses a single key id and in my case I have to use joins from multiple tables.

Here is the layout for how the tables look.

CREATE TABLE driven_product
([PRODUCT_ID] int, [DRIVER_ID] int, [DRIVER_PRODUCT_INPUT_NUM] int);

INSERT INTO driven_product
([PRODUCT_ID], [DRIVER_ID], [DRIVER_PRODUCT_INPUT_NUM])
VALUES (1, 2, 3);

CREATE TABLE product_input
([PRODUCT_ID] int, [PRODUCT_INPUT_NUM] int, [PRODUCT_VALUE_NUM] int, [COLOR] VARCHAR (50));

INSERT INTO product_input
([PRODUCT_ID], [PRODUCT_INPUT_NUM], [PRODUCT_VALUE_NUM], [COLOR])
VALUES
(1, 3, 1, 'White'),
(1, 3, 2, 'Blue'),
(1, 3, 3, 'Green'),
(1, 3, 4, 'Yellow'),
(1, 3, 5, 'Orange');

CREATE TABLE driven_price
[PRODUCT_ID] int, [DRIVER_ID] int, [PRODUCT_VALUE_NUM] int, [PRICE] int);

INSERT INTO driven_price
([PRODUCT_ID], [DRIVER_ID], [PRODUCT_VALUE_NUM], [PRICE])
VALUES
(1, 2, 1, 10),
(1, 2, 2, 10),
(1, 2, 3, 10),
(1, 2, 4, 20),
(1, 2, 5, 20);


The driven_product table joins to the product_input table using driven_product.product_id = product_input.product_id AND driven_product.driver_product_input_num = product_input.product_input_num. The driven_price table joins using the
driven_product.product_id = driven_price.product_id, driven_product.driver_id = driven_price.driver_id, and product_input.product_value_num = driven_product.product_value_num.

The closest I have gotten to is:

SELECT STUFF((SELECT '/' + color
FROM product_input pi
WHERE pi.product_id = dp.product_id
AND pi.product_input_num = dp.product_input_num
FOR XML PATH( '')), 1, 1, ''), dpr.price
FROM driven_product dp
INNER JOIN driven_price dpr ON dp.product_id = dpr.product_id
AND dp.driven_id = dpr.driven_id


This combines all the colors into each price.

Now the obvious thing is that I am not joining the product_input.product_value_num to the driven_price.product_value_num. When I do that it breaks each color out into its own row.

So this is where I am struggling is that I need to do it by price. So I need to have "White, Blue, Green" and "Yellow, Orange" to be separate.

I tried to set this up on SQLFiddle, but I kept getting errors. Any guidance that you can provide will be appreciated.

Answer

you can use group by or distinct.. but your main problem is you were not filtering your FOR XML query by PRICE, so you're getting every color.

SELECT  DISTINCT
        Products = STUFF((
                SELECT '/' + color
                FROM    driven_price dp2
                        JOIN product_input pi ON dp2.Product_Value_Num = PI.Product_Value_Num
                WHERE   dp2.driver_id = dpr.driver_id AND dp2.Price = dp.Price
                FOR XML PATH('')), 1, 1, ''),
        dp.[Price]
FROM    driven_product dpr
        JOIN product_input pri ON dpr.Driver_product_input_num = pri.PRODUCT_INPUT_NUM
        JOIN driven_price dp ON pri.product_id = dp.product_id 
            AND pri.product_value_num = dp.product_value_num
Comments