问题描述
很抱歉在这里没有答案，但是我找不到它...可以使用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（）连接行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！