Combine multiple ROWS into Comma Separated String… and vice-versa
There are times when you want to combine values from multiple rows of a single column into a CSV (Comma Separates Value) String.
Like combining multiple orders of each customer into one single row, or multiple Cities against each State as shown below.
It was a bit difficult to do in SQL Server 2000 and requires to create a customized function or a script.
But with SQL Server’s 2005 version, the new “PATH” mode with “FOR XML” provides us a way to get the desired output. Its a bit tricky but will do the needful, that’s why I’ve also added this post for my reference as well.