How to combine multiple rows into one row with multiple…

1 year ago
Benjamin Taylor
1 minute

In SQL Server, the FOR XML PATH clause can be used to concatenate multiple rows into a single row with multiple columns. Here is an example:

Assuming there is a table named Employees, which includes the following data:

ID    Name       Department
--------------------------
1     John       IT
2     Mary       HR
3     David      Finance

You can use the following query to merge the above data into a single row with multiple columns of results:

SELECT STUFF((SELECT ',' + Name
              FROM Employees
              FOR XML PATH('')), 1, 1, '') AS Names

The results are as follows: 

Names
-----------------
John,Mary,David

In the above query, the subquery converts each Name value into a comma-separated string using FOR XML PATH(”). Then, the STUFF function is used to replace the first comma with an empty string, resulting in the final merged result.

bannerAds