使用来自多个表的FOR XML PATH（）连接行 [英] Concatenate Rows using FOR XML PATH() from multiple tables

问题描述

很抱歉在这里没有答案，但是我找不到它...可以使用FOR XML PATH连接多个表中的行吗？让我解释一下。...

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

我有以下4个表：

BusinessSupport具有参考到其他3个表

"BusinessSupport" which has reference to the other 3 tables

应用程序， BusinessProcess和 OrgaUnit（包含组织信息，例如服务热线和地理位置）

"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

app1;流程1，流程2，流程3； SL1，SL2，SL3

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

app2;流程1，流程2； SL1，SL4，SL4

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

app3; process4，过程2； SL3，SL5，SL6

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

但是，我现在有一个新的要求我向其报告。
表5关系中提到了业务支持和Orgaunit。

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.

我想要类似的东西

app1;流程1，流程2，流程3； SL1，SL2，SL3； Geography1，Geography2

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

app2;流程1，流程2； SL1，SL4，SL4； Geography1，Geography3

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

app3; process4，过程2； SL3，SL5，SL6； Geography3，Geography4，Geography5

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]

谢谢

推荐答案

如果使用 FOR XML 连接值时返回的任何列返回 NULL，，则整个连接字符串将变为 NULL ，因为某些字符串 + NULL 始终为 NULL 是SQL Server。如果在示例中 org2.name 可能为 NULL ，则包装 ISNULL 并置一个空字符串。

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]

这篇关于使用来自多个表的FOR XML PATH（）连接行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！