How to Concatenate Values Within Groups in SQL Server?

Join groups in SQL Server

To join values ​​within groups in SQL Server 2005, consider using user-defined aggregate functions.

Solution:

1. Create a user-defined aggregate function (UDAF):

   <code class="language-sql"> CREATE AGGREGATE [dbo].[ConcatValues] (@str VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
        DECLARE @result VARCHAR(MAX) = '',
                @delim VARCHAR(2) = '';
        WHILE @str IS NOT NULL
        BEGIN
            SET @result = @result + @delim + @str;
            SET @delim = ',';
            SET @str = NEXT VALUE;
        END;
        RETURN @result;
    END;</code>

2. Use UDAF in queries:

   <code class="language-sql"> SELECT Id, [dbo].[ConcatValues](Value) AS ConcatenatedValues
    FROM YourTable
    GROUP BY Id;</code>

Result:

Id	ConcatenatedValues
1	'A,B'
2	'C'

Note that this solution uses a WHILE loop to iterate over the input values ​​and concatenate them using commas as separators. This is different from the FOR loop in the original example, but implements the same functionality and is more common in SQL Server. NEXT VALUE is used to get the next input value of the aggregate function. YourTable should be replaced with your actual table name.

The above is the detailed content of How to Concatenate Values Within Groups in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!