CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
user1817497
user1817497

Reputation: 11

SQL Concatenation using XML Path - multiple rows with multiple table references

I am trying to use the XML Path to run SQL Concatenation but I am running into a bit of a problem. I have one table that is being used as a reference table for values I want to concatenate.I have 3 columns in the reference table (M.PROD, S.PROD, & REF NUMB).

I have the main tables where the m.prod and s.prod are used to match the reference table for Ref Numb values. What I want to do is concatenate the Ref Numb values based on what is being selected in main tables. The out put I am looking for is this:

I am using the following query: 

SELECT DISTINCT P.PRODUCT, 
    (STUFF((SELECT DISTINCT ',' + P1.REFNUMB AS [text()]
            FROM PRODUCT P1
            WHERE P1.PRODUCT = P.PRODUCT 
            FOR XML PATH('')), 1, 1, ''))
FROM PRODUCT P

This gives me the output of:

However, there are times where all the s.prod are not in the main tables. So for this I use this query: 

SELECT DISTINCT P.PRODUCT, 
    (STUFF((SELECT DISTINCT ',' + P1.REFNUMB AS [text()]
            FROM PRODUCT P1
            WHERE P1.PRODUCT = P.PRODUCT AND P1.SUBID = P.SUBID
            FOR XML PATH('')), 1, 1, ''))
FROM PRODUCT P

This query produces following output for me:

The output I need in these cases is:

Any solution for this will be greatly appreciated, Thank you.

Upvotes: 1

Views: 3975

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

I'm not sure if I understood your question correctly, but try this:

SELECT DISTINCT P.PRODUCT, 
    (STUFF((SELECT DISTINCT ',' + P1.REFNUMB AS [text()]
            FROM PRODUCT P1
            WHERE P1.PRODUCT = P.PRODUCT 
            AND (P1.SUBID = P.SUBID OR P1.SUBID IS NULL AND P.SUBID IS NULL)
            FOR XML PATH('')), 1, 1, ''))
FROM PRODUCT P

Upvotes: 1

Related Questions