Concatenate Rows using FOR XML PATH() from multiple tables

Question

Apologies if there was an answer in here, but I cannot find it...Can you concatenate rows from multiple tables using FOR XML PATH. Let me explain....

I have the following 4 tables:

"BusinessSupport" which has reference to the other 3 tables

"Application", "BusinessProcess" and "OrgaUnit" (contains organizational info such as Service Line and Geography)

SELECT  a.NAME AS [App Name],            

        STUFF((SELECT ',' + bp.NAME as [text()] 
        FROM BUSINESSPROCESS bp 
        LEFT JOIN BUSINESSSUPPORT bs on  bp.REFSTR=bs.XOBJECT   
        WHERE bs.OBJECT=a.REFSTR FOR XML PATH('')), 1, 1, '' ) AS [Business Process]    ,

        STUFF((SELECT ',' + org.NAME as [text()] 
        FROM ORGAUNIT org 
        LEFT JOIN BUSINESSSUPPORT bs on org.REFSTR=bs.YOBJECT  
        WHERE bs.OBJECT=a.REFSTR FOR XML PATH('')), 1, 1, '' ) AS [Service Lines]   

 FROM APPLICATION a

This gives me table results such as

AppName; Business Process; Service Lines

app1; process1, process2, process 3; SL1, SL2, SL3

app2; process1, process 2; SL1, SL4, SL4

app3; process4, process 2; SL3, SL5, SL6

However, I now have a new dimension I have been asked to report from. Table 5 "Relations" which has a reference to Business Support and Orgaunit.

I would like to have something like

AppName; Business Process; Service Lines; Geography

app1; process1, process2, process 3; SL1, SL2, SL3; Geography1, Geography2

app2; process1, process 2; SL1, SL4, SL4; Geography1, Geography3

app3; process4, process 2; SL3, SL5, SL6; Geography3, Geography4, Geography5

I have tried the following but do not get any results returned:

STUFF((SELECT ',' + org2.name as [text()] 
FROM ORGAUNIT org2 
LEFT JOIN RELATIONS rel ON rel.TOREF=org2.name 
LEFT JOIN BUSINESSSUPPORT bs on bs.REFSTR=rel.FROMREF  
        WHERE bs.OBJECT=a.REFSTR FOR XML PATH('')), 1, 1, '' ) AS [Geog]

Thanks

Have you checked that the SELECT inside your STUFF actually returns records? You should get that query working first. — Mr. Mascaro
– Mr. Mascaro, Commented Nov 5, 2014 at 13:44
Yep query is fine. If I return A Geog ID as opposed to Geog name it all works fine. But it does not have that extra JOIN to get the GEOG name STUFF((SELECT ',' + rel.TOREF as [text()] FROM RELATIONS rel LEFT JOIN BUSINESSSUPPORT bs on bs.REFSTR=rel.FROMREF WHERE bs.OBJECT=a.REFSTR FOR XML PATH('')), 1, 1, '' ) AS [Geog ID] — user3302303
– user3302303, Commented Nov 5, 2014 at 13:57
Please update your answer with the query you used to test it. — Mr. Mascaro
– Mr. Mascaro, Commented Nov 5, 2014 at 13:58

BateTech · Accepted Answer · 2014-11-05 13:58:43Z

-1

If any of your columns returned when using FOR XML to concatenate values return a NULL, then the entire concatenated string will become NULL, since "some string" + NULL is always NULL is SQL Server. If it is possible for org2.name to be NULL in your example, then wrap an ISNULL around the concatenated results, and replace with an empty string.

STUFF((SELECT ISNULL(',' + org2.name, '') as [text()] 
FROM ORGAUNIT org2 
LEFT JOIN RELATIONS rel ON rel.TOREF=org2.name 
LEFT JOIN BUSINESSSUPPORT bs on bs.REFSTR=rel.FROMREF  
        WHERE bs.OBJECT=a.REFSTR FOR XML PATH('')), 1, 1, '' ) AS [Geog]

answered Nov 5, 2014 at 13:58

BateTech

6,6764 gold badges24 silver badges33 bronze badges

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

2 Comments

user3302303 Over a year ago

Thanks. I understand the logic, however, the use of ISNull does not work when I adjust the code :( Even changing the empty string for a random string value still gives me a NULL value?

BateTech Over a year ago

add a.REFSTR to your main query result set, and then for one of the results that returns a NULL for the Geography, try this query as see what results you get: SELECT org2.name as [text()] FROM ORGAUNIT org2 LEFT JOIN RELATIONS rel ON rel.TOREF=org2.name LEFT JOIN BUSINESSSUPPORT bs on bs.REFSTR=rel.FROMREF WHERE bs.OBJECT= {PutValue of a.refstr here}; If this returns 0 records, then there is an issue with your subquery having incorrect joins to get back to the Geography name.

Collectives™ on Stack Overflow

Concatenate Rows using FOR XML PATH() from multiple tables

AppName; Business Process; Service Lines

AppName; Business Process; Service Lines; Geography

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

AppName; Business Process; Service Lines

AppName; Business Process; Service Lines; Geography

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related