FOR XML PATH - How to generate a Delimited String using FOR XML PATH?

There are two common string operations where I used to write a TSQL loop in the SQL server 2000 era.

1. To split a delimited string and return a set
2. To generate a delimited string from a set

The XML enhancements added to SQL Server 2005 made both these operations easier with XML. I think, most of the times these operations are done in small pieces of data. Though you can do these operations on extremely large data, I don't think it is advisable. There are other ways to handle large chunks of data.

In this post, lets see how we could generate a delimited string using FOR XML PATH.

Let us first see the source data.

DECLARE @companies Table( 
 CompanyID INT, 
 CompanyCode  int
)

insert into @companies(CompanyID, CompanyCode) values(1,1)
insert into @companies(CompanyID, CompanyCode) values(1,2)
insert into @companies(CompanyID, CompanyCode) values(2,1)
insert into @companies(CompanyID, CompanyCode) values(2,2)
insert into @companies(CompanyID, CompanyCode) values(2,3)
insert into @companies(CompanyID, CompanyCode) values(2,4)
insert into @companies(CompanyID, CompanyCode) values(3,1)
insert into @companies(CompanyID, CompanyCode) values(3,2)

SELECT * FROM @companies
/*
CompanyID   CompanyCode
----------- -----------
1           1
1           2
2           1
2           2
2           3
2           4
3           1
3           2
*/

This is the result that we need.

/*
CompanyID   CompanyString
----------- -------------------------
1           1,2
2           1,2,3,4
3           1,2
*/

One option is to run a loop that constructs a delimited string for each CompanyID. Another option is to create a function that returns a delimited string for each company ID. I am presenting a third option using FOR XML PATH.

SELECT CompanyID,
 REPLACE((SELECT
     CompanyCode AS 'data()'
  FROM @companies c2
  WHERE c2.CompanyID = c1.CompanyID
  FOR XML PATH('')), ' ', ',') AS CompanyString
FROM @companies c1
GROUP BY CompanyID

/*
CompanyID   CompanyString
----------- -------------------------
1           1,2
2           1,2,3,4
3           1,2

The above query uses FOR XML PATH to return a comma delimited string containing the company
code of each row.
*/