How to Combine Multiple Rows into a Comma-Delimited String in SQL Server 2005?

Achieving Comma-Delimited Row Combination in SQL Server 2005

When dealing with SQL data, there may arise the need to combine multiple rows into a single comma-delimited list. To achieve this in SQL Server 2005, one efficient method is employed.

Approach:

Consider a sample dataset like the one described in the query:

SELECT X, Y FROM POINTS

which yields a result as follows:

X     Y
----------
12     3
15     2
18     12
20     29

To create a comma-delimited string from these rows, we can use the FOR XML PATH statement:

SELECT STUFF(( SELECT ',' + X + ',' + Y
                FROM Points
              FOR
                XML PATH('')
              ), 1, 1, '') AS XYList

This statement generates an XML representation of the data in concatenated form. By removing the leading comma (',') using the STUFF function, we obtain the desired comma-delimited list.

For example, the sample dataset will produce the following output:

XYList
----------
12,3,15,2,18,12,20,29

This method allows for efficient row combination in SQL Server 2005, providing a convenient way to prepare data for various purposes, such as display in HTML tags.

The above is the detailed content of How to Combine Multiple Rows into a Comma-Delimited String in SQL Server 2005?. For more information, please follow other related articles on the PHP Chinese website!