CODEWITHSUNDEEP
sqlsql-server
Derek Hansen
Derek Hansen

Reputation: 7

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.

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You need an aggregation in the outer query. I think this will do what you want:

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

Note: You only need stuff() to handle infix separators. If you are happy with "/blue/green/red", then you don't need it. The purpose of stuff() is to remove a leading separated, so ",blue,green,red" becomes "blue,green,red".

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

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

Upvotes: 1

Related Questions