sql - 如何使用 STUFF 和 FOR XML PATH 分隔与其行相关的唯一值
问题描述
我正在运行这个查询:
SELECT A.PeripheralNumber, P.LoginName, P.FirstName, P.LastName, A.EnterpriseName, A.AgentDeskSettingsID, AttributeValues =
STUFF((SELECT ',' + ATT.EnterpriseName+'='+AT.AttributeValue AS Attribute
FROM Agent_Attribute AT
LEFT JOIN Agent A ON A.SkillTargetID = AT.SkillTargetID
LEFT JOIN Attribute ATT ON ATT.AttributeID = AT.AttributeID
WHERE A.SkillTargetID IN ('26379','24111')
ORDER BY Attribute
FOR XML PATH (''), TYPE ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM Agent A
LEFT JOIN Person P ON P.PersonID = A.PersonID
LEFT JOIN Agent_Desk_Settings ADS ON ADS.AgentDeskSettingsID = A.AgentDeskSettingsID
WHERE A.SkillTargetID IN ('26379','24111')
AND A.EnterpriseName LIKE 'PG_8%'
AND P.FirstName NOT LIKE '%Test%'
GROUP BY A.PeripheralNumber, P.LoginName, P.FirstName, P.LastName, A.EnterpriseName, A.AgentDeskSettingsID
我得到的结果如下:
|PeripheralNumber|LoginName|FirstName|LastName|EnterpriseName|AgentDeskSettingsID|AttributeValues |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|551628 |551628 |John |Doe |Doe_John |5104 |AAH_CSC_EXIST=5,AAH_CSC_FNOL=5,AAH_CSC_ROTL=5,AAH_SERVICE_AUTO=10,AAH_SERVICE_REBRAND=5,AAH_SERVICE_WEB_ONLINE=10|
|558792 |558792 |Jane |Doe |Doe_Jane |5103 |AAH_CSC_EXIST=5,AAH_CSC_FNOL=5,AAH_CSC_ROTL=5,AAH_SERVICE_AUTO=10,AAH_SERVICE_REBRAND=5,AAH_SERVICE_WEB_ONLINE=10|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
正在发生的事情是正在合并 John Doe 和 Jane Doe 的唯一 AttributeValues。我需要的结果如下:
|PeripheralNumber|LoginName|FirstName|LastName|EnterpriseName|AgentDeskSettingsID|AttributeValues |
------------------------------------------------------------------------------------------------------------------------------------------------------
|551628 |551628 |John |Doe |Doe_John |5104 |AAH_SERVICE_AUTO=10,AAH_SERVICE_REBRAND=5,AAH_SERVICE_WEB_ONLINE=10|
|558792 |558792 |Jane |Doe |Doe_Jane |5103 |AAH_CSC_EXIST=5,AAH_CSC_FNOL=5,AAH_CSC_ROTL=5 |
------------------------------------------------------------------------------------------------------------------------------------------------------
帮助!这是 MS SQL Server 2016
解决方案
你cross apply
或outer apply
. 像这样的东西:
SELECT DISTINCT A.PeripheralNumber, P.LoginName, P.FirstName, P.LastName, A.EnterpriseName, A.AgentDeskSettingsID, DS.AttributeValues
FROM Agent A
LEFT JOIN Person P
ON A.PersonID = P.PersonID
LEFT JOIN Agent_Desk_Settings ADS
ON A.AgentDeskSettingsID = ADS.AgentDeskSettingsID
OUTER APPLY
(
SELECT STUFF((SELECT ',' + ATT.EnterpriseName+'='+AT.AttributeValue AS Attribute
FROM Agent_Attribute AT
INNER JOIN Attribute ATT
ON ATT.AttributeID = AT.AttributeID
WHERE A.SkillTargetID = AT.SkillTargetID
ORDER BY Attribute
FOR XML PATH (''), TYPE ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
) DS (AttributeValues)
WHERE A.SkillTargetID IN ('26379','24111')
AND A.EnterpriseName LIKE 'PG_8%'
AND P.FirstName NOT LIKE '%Test%'
推荐阅读
- spring-boot - 在运行时根据文件扩展名设置目标位置
- c# - 如何在 SQL Server 通信中禁用 C# 中的 NetBios
- sql-server - 如何修复 System.Data.SqlClient.SqlException:“用户“xx\xxxx$”登录失败”
- c - 结构填充的规则是什么
- html - 显示 HTML5 进度条的内部文本?没有实施的技术原因?
- asp.net-mvc - 如何使用实体框架连接两个表
- r - 有没有办法在换出一个变量时循环 R 分类模型?
- python - 从数据框中读取和拆分列值
- android - 白天晚上忽略特定活动
- laragon - Laragon 调用未定义函数 pg_pconnect() 即使我启用了 pgsql