0

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

iminiki
  • 2,549
  • 12
  • 35
  • 45
user3302303
  • 15
  • 1
  • 3
  • Have you checked that the `SELECT` inside your `STUFF` actually returns records? You should get that query working first. – Mr. Mascaro Nov 05 '14 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 Nov 05 '14 at 13:57
  • Please update your answer with the query you used to test it. – Mr. Mascaro Nov 05 '14 at 13:58

1 Answers1

-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]
BateTech
  • 5,780
  • 3
  • 20
  • 31
  • 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? – user3302303 Nov 05 '14 at 14:07
  • 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. – BateTech Nov 07 '14 at 16:13