String concatenation and entitization
This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog.
Consider this sample data
DECLARE @Sample TABLE
(
ID INT,
Data VARCHAR(100)
)
INSERT @Sample
VALUES (1, 'Peso & Performance SQL'),
(1, 'MVP'),
(2, 'Need help <? /> -- '),
(2, 'With XML string concatenation ?')
The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out.
So here is the final query.
SELECT i.ID,
The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out.
So here is the final query.
SELECT i.ID,
STUFF(f.Content.value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM (
SELECT ID
FROM @Sample
GROUP BY ID
) AS i
CROSS APPLY (
SELECT ', ' + w.Data
FROM @Sample AS w
WHERE w.ID = i.ID
FOR XML PATH(''),
TYPE
) AS f(Content)
And to deal with characters having ascii values less than 32 (space), you can use this
;WITH cteSource(ID, Content)
;WITH cteSource(ID, Content)
AS (
SELECT i.ID,
f.Content.value('.', 'NVARCHAR(MAX)')
FROM (
SELECT ID
FROM @Sample
GROUP BY ID
) AS i
CROSS APPLY (
SELECT CAST(', ' + w.Data AS VARBINARY(MAX))
FROM @Sample AS w
WHERE w.ID = i.ID
FOR XML PATH(''),
TYPE
) AS f(Content)
)
SELECT ID,
STUFF(CAST(Content AS NVARCHAR(MAX)), 1, 2, '') AS Content
FROM (
SELECT ID,
CAST(N'' AS XML).value('xs:base64Binary(sql:column("Content"))', 'VARBINARY(MAX)') AS Content
FROM cteSource
) AS d