FOR XML 1 column with multiple tables

Transact-SQL
, , , ,
1

Hi,
My query's output needs the Entity.Name value combined into one column called ExtAccessLevelID, separated by commas. Currently, every Entity.Name value that is separate appears in a separate row, giving me multiple results for the same person.
I've been working at getting the desired result with FOR XML, but am unsure how to get it to work with 5 tables. I've included my most basic query as a start.

So the end result should be: Smith John xxx xxxx xxxx name1,name2,name4

Any help / direction would be appreciated!
Thank you

Select
Cardholder.Guid,
Replace(Cardholder.Guid, '-', '') As 'ExtUserID',
Cardholder.FirstName,
Cardholder.LastName,
Cardholder.ExtendedGrantTime As 'ExtendedOpeningTime',
Cardholder.ExpirationDate As 'UserExpiration.ExpDate',
Credential.UniqueID As 'WeigandCode',
Entity.Name As 'ExtAccessLevelIDList',
Case When Credential.Status > 0 Then '8' Else Null End As 'Action'
From
Cardholder Left Outer Join
Credential On Credential.Cardholder = Cardholder.Guid Inner Join
CardholderMembership On Cardholder.Guid = CardholderMembership.GuidMember
Inner Join
CardholderGroup On CardholderMembership.GuidGroup = CardholderGroup.Guid
Inner Join
Entity On CardholderGroup.Guid = Entity.Guid
Where
Entity.Type = 8

2

The extraneous columns that are not not relevant to the concatenation task makes it somewhat difficult for me to understand the result you are looking for. Based on my limited understanding, perhaps this is what you want?

;WITH cte AS
(
	SELECT  Cardholder.Guid ,
			REPLACE(Cardholder.Guid, '-', '') AS 'ExtUserID' ,
			Cardholder.FirstName ,
			Cardholder.LastName ,
			Cardholder.ExtendedGrantTime AS 'ExtendedOpeningTime' ,
			Cardholder.ExpirationDate AS 'UserExpiration.ExpDate' ,
			Credential.UniqueID AS 'WeigandCode' ,
			Entity.Name AS 'ExtAccessLevelIDList' ,
			CASE WHEN Credential.Status > 0 THEN '8'
				 ELSE NULL
			END AS 'Action'
	FROM    Cardholder
			LEFT OUTER JOIN Credential ON Credential.Cardholder = Cardholder.Guid
			INNER JOIN CardholderMembership ON Cardholder.Guid = CardholderMembership.GuidMember
			INNER JOIN CardholderGroup ON CardholderMembership.GuidGroup = CardholderGroup.Guid
			INNER JOIN Entity ON CardholderGroup.Guid = Entity.Guid
	WHERE   Entity.Type = 8
)
SELECT
	a.FirstName,
	a.LastName,
	STUFF(b.ExtAccessLevelIDLists,1,1,'') AS ExtAccessLevelIDLists
FROM
	(SELECT DISTINCT FirstName, LastName, [Guid] FROM cte) AS a
	CROSS APPLY
	(
		SELECT ',' + ExtAccessLevelIDList
		FROM cte b
		WHERE b.[Guid] = a.[Guid]
	) b(ExtAccessLevelIDLists);
3

Pedantic point, not relevant to the problem being discussed, but if this will be generating comma delimited data I'd want a REPLACE on all Var/Char columns, such as FirstName / LastName, to "escape" any embedded commas

4

Hi, I apologize for the extra. The following should be much easier to follow:

SELECT  Cardholder.Guid,
        Cardholder.FirstName,
        Cardholder.LastName,
        Cardholder.ExtendedGrantTime,
        Cardholder.ExpirationDate,
        Credential.UniqueID,
        Entity.Name,
        Credential.Status
FROM    Cardholder
        LEFT OUTER JOIN Credential ON Credential.Cardholder = Cardholder.Guid
        INNER JOIN CardholderMembership ON Cardholder.Guid = CardholderMembership.GuidMember
        INNER JOIN CardholderGroup ON CardholderMembership.GuidGroup = CardholderGroup.Guid
        INNER JOIN Entity ON CardholderGroup.Guid = Entity.Guid

WHERE Entity.Type = 8

So I have a basic select statement, bringing each of these values together. Each of these columns only have 1 value, with the exception of my Entity.Name, which can have multiple values. This column, in the query, needs to have all the values combined, separated by commas. Basically, a concatenation of rows.

5

If you posted some data, as CREATE TABLE / INSERT, I could have posted a working example for you. I aint spending my time to re-type what you ahve posted in an IMAGE ... sorry!

Here's an example of FOR XML which gives a delimited list

-- Concatenating delimited list using XML
DECLARE	@MyHeaderData TABLE
(
	MyKey	varchar(10),
	MyName	varchar(20)
)

DECLARE	@MyChildData TABLE
(
	MyKey	varchar(10),
	MyValue	varchar(20)
)

INSERT INTO @MyHeaderData(MyKey, MyName)
SELECT	'AAA', 'A Name' UNION ALL
SELECT	'BBB', 'B Name' UNION ALL
SELECT	'HTML', 'Need escaping'

INSERT INTO @MyChildData(MyKey, MyValue)
SELECT	'AAA', 'A1' UNION ALL
SELECT	'AAA', 'A2' UNION ALL
SELECT	'AAA', 'A3' UNION ALL
SELECT	'BBB', 'B1' UNION ALL
SELECT	'BBB', 'B2' UNION ALL
SELECT	'BBB', 'B3' UNION ALL
SELECT	'HTML', 'LT < LT' UNION ALL
SELECT	'HTML', 'GT > GT' UNION ALL
SELECT	'HTML', 'AMP & AMP' UNION ALL
SELECT	'HTML', 'AMP2 &amp; AMP2'

SELECT	MyKey
	, MyName
	, STUFF(
		(
			SELECT	',' + MyValue
			FROM	@MyChildData AS C
			WHERE	C.MyKey = H.MyKey
			ORDER BY MyValue
			FOR XML PATH(''), TYPE
		).value('.', 'varchar(max)')
	        , 1, 1, '') AS MyValueList
FROM	@MyHeaderData AS H
1 Like
6

Hi Kristen,
Thank you for your help! I am new to asking help of this kind as I am learning myself. Next time, I'll include some sample values.
I was able to take your example and translate it into exactly what I needed by inputting my values. The following returned exactly what I needed. I'll need to clean it up a bit, but it works!

 DECLARE    @MyHeaderData TABLE
(
    V1    varchar(40),
    V2    varchar(40),
    V3  varchar(40),
    V4  varchar(40),
    V5  DATETIME,
    V6  varchar(40),
    V8  varchar(40)
)

DECLARE    @MyChildData TABLE
(
    V1    varchar(40),
    VE  varchar(1000)
)

INSERT INTO @MyHeaderData(V1, V2, V3, V4, V5, V6, V8)
SELECT        
        Replace (Cardholder.Guid, '-',''),
        Cardholder.FirstName,
        Cardholder.LastName,
        Cardholder.ExtendedGrantTime,
        Cardholder.ExpirationDate,
        Credential.UniqueID,
        Credential.Status
FROM    Cardholder
        LEFT OUTER JOIN Credential ON Credential.Cardholder = Cardholder.Guid
        INNER JOIN CardholderMembership ON Cardholder.Guid = CardholderMembership.GuidMember
        INNER JOIN CardholderGroup ON CardholderMembership.GuidGroup = CardholderGroup.Guid
        INNER JOIN Entity ON CardholderGroup.Guid = Entity.Guid
UNION ALL
SELECT    'HTML', 'Need escaping', '3', '4', GETDATE(), '6', '8'

INSERT INTO @MyChildData(V1, VE)
SELECT        
        Replace (Cardholder.Guid, '-',''),
        Entity.Name
FROM    Cardholder
        LEFT OUTER JOIN Credential ON Credential.Cardholder = Cardholder.Guid
        INNER JOIN CardholderMembership ON Cardholder.Guid = CardholderMembership.GuidMember
        INNER JOIN CardholderGroup ON CardholderMembership.GuidGroup = CardholderGroup.Guid
        INNER JOIN Entity ON CardholderGroup.Guid = Entity.Guid
UNION ALL

SELECT    'HTML', 'LT < LT' UNION ALL
SELECT    'HTML', 'GT > GT' UNION ALL
SELECT    'HTML', 'AMP & AMP' UNION ALL
SELECT    'HTML', 'AMP2 &amp; AMP2'

SELECT    
      V1
    , V2
    , V3
    , V4
    , V5
    , V6
    , V8
    , STUFF(
        (
            SELECT    ',' + VE
            FROM    @MyChildData AS C
            WHERE    C.V1 = H.V1
            ORDER BY VE
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
            , 1, 1, '') AS MyValueList
FROM    @MyHeaderData AS H
GROUP BY
V1, V2, V3, V4, V5, V6, V8

Best regards! And thank you again :smile: