Microsoft SQL Server 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 BY clause can be used to order the values in a preferred way
  • if a longer value is used as the concatenation separator, the STUFF function 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 TYPE option and .value function are used, the concatenation works with NVARCHAR(MAX) string