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

Question

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

M.PROD=====>S.PROD======>Ref Numb
1===========>_===========>981024583
2===========>_===========>981024719
3===========>A===========>981024605
3===========>B===========>981024669
4===========>A===========>981024688
4===========>B===========>981024706
4===========>C===========>981024723
5===========>_===========>981024742
6===========>_===========>981024742

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:

M.Prod======>Ref Numb
1===========>981024583
2===========>981024719
3===========>981024605, 981024669
4===========>981024688, 981024706, 981024723
5===========>981024742
6===========>981024742

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:

M.Prod======>Ref Numb
1===========>981024583
2===========>981024719
3===========>981024605, 981024669
4===========>981024688, 981024706, 981024723
5===========>981024742
6===========>981024742

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:

M.Prod======>Ref Numb
1===========>NULL
2===========>NULL
3===========>981024605
3===========>981024669
4===========>981024688
4===========>981024723
5===========>NULL
6===========>NULL

The output I need in these cases is:

M.Prod======>Ref Numb
1===========>981024583
2===========>981024719
3===========>981024605, 981024669
4===========>981024688, 981024723
5===========>981024742
6===========>981024742

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

Razvan Socol · Accepted Answer · 2012-12-22 17:11:15Z

1

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

answered Dec 22, 2012 at 17:11

Razvan Socol

5,7592 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related