sqlsql-serverxmlsql-server-2016stuff

How to separate unique values respective to its row using STUFF and FOR XML PATH


I'm running this query:

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

The result I'm getting is the following:

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

What's happening is that the unique AttributeValues for John Doe and Jane Doe are being combined. The result I need is the following:

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

Help! This is MS SQL Server 2016


Solution

  • You cross apply or outer apply. Something like this:

    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%'