How to Concatenate Grouped Values in SQL Server Using a User-Defined Aggregate Function?

SQL Server String Aggregation with User-Defined Aggregate Functions

Need to concatenate strings within groups in SQL Server, similar to MySQL's GROUP_CONCAT? SQL Server 2005 and later versions don't have a built-in equivalent, requiring a more advanced solution. A user-defined aggregate function (UDAF) provides an effective approach.

Consider this sample data:

Id	Value
1	'A'
1	'B'
2	'C'

The goal is to achieve this output:

Id	Value
1	'AB'
2	'C'

Creating the UDAF

The following code defines a UDAF named AggregateConcat to perform the string concatenation within each group:

<code class="language-sql">CREATE FUNCTION AggregateConcat (@id INT, @value VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @result VARCHAR(MAX) = '';
    SELECT @result = @result + Value
    FROM TABLE_NAME
    WHERE Id = @id;
    RETURN @result;
END;</code>

Using the UDAF

This function can be used in a SELECT statement like any other aggregate function:

<code class="language-sql">SELECT Id, dbo.AggregateConcat(Id, Value) AS ConcatenatedValue
FROM TABLE_NAME
GROUP BY Id;</code>

This query groups the data by Id and applies the AggregateConcat function to concatenate the Value column for each group, producing the desired result. Remember to replace TABLE_NAME with your actual table name. This simplified version avoids the less efficient cursor-based approach. For very large datasets, consider alternative, more performant methods like using STRING_AGG (available in SQL Server 2017 and later).

The above is the detailed content of How to Concatenate Grouped Values in SQL Server Using a User-Defined Aggregate Function?. For more information, please follow other related articles on the PHP Chinese website!