Microsoft SQL Server FOR XML PATH Using FOR XML PATH to concatenate values
Example
The FOR XML PATH can be used for concatenating values into string. The example below concatenates values into a CSV string:
DECLARE @DataSource TABLE
(
[rowID] TINYINT
,[FirstName] NVARCHAR(32)
);
INSERT INTO @DataSource ([rowID], [FirstName])
VALUES (1, 'Alex')
,(2, 'Peter')
,(3, 'Alexsandyr')
,(4, 'George');
SELECT STUFF
(
(
SELECT ',' + [FirstName]
FROM @DataSource
ORDER BY [rowID] DESC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,1
,''
);
Few important notes:
- the
ORDER BYclause can be used to order the values in a preferred way - if a longer value is used as the concatenation separator, the
STUFFfunction parameter must be changed too;
SELECT STUFF
(
(
SELECT '---' + [FirstName]
FROM @DataSource
ORDER BY [rowID] DESC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,3 -- the "3" could also be represented as: LEN('---') for clarity
,''
);
- as the
TYPEoption and.valuefunction are used, the concatenation works withNVARCHAR(MAX)string
