How to Concatenate Grouped Values in SQL Server 2005?

Merge grouped values ​​in SQL Server

Suppose you have a table that contains multiple values ​​associated with a unique identifier, for example:

<code>+------------+
| Id | Value |
+------------+
| 1  | 'A'   |
|------------|
| 1  | 'B'   |
|------------|
| 2  | 'C'   |
+------------+</code>

To achieve the desired result of merging rows with the same Id into one row with the combined value:

<code>+------------+
| Id | Value |
+------------+
| 1  | 'AB'  |
|------------|
| 2  | 'C'   |
+------------+</code>

In SQL Server 2005, it is possible to use user-defined aggregate functions. This function is provided in its sample implementation to perform this concatenation task seamlessly.

-- 创建用户定义的聚合函数
CREATE FUNCTION [dbo].[ConcatValues](@array VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @result VARCHAR(MAX) = '';
    IF @array IS NOT NULL
    BEGIN
        SELECT @result = @result + value
        FROM STRING_SPLIT(@array, ',');
    END
    RETURN @result;
END;

-- 在查询中使用用户定义的聚合函数
SELECT Id, [dbo].[ConcatValues](STRING_AGG(Value, ',')) AS Value
FROM TableName
GROUP BY Id;

With this solution, you can leverage user-defined aggregate functions just like any other standard aggregate function, allowing you to incorporate them seamlessly into your queries. Note that the STRING_SPLIT function is available in SQL Server 2016 and later. For SQL Server 2005, you need to use an alternative method to split the string, such as a custom function or a cursor. The code above has been updated to use the STRING_AGG function (SQL Server 2017 and later), providing a clearer solution. For SQL Server 2005, you will need a more complex custom solution to emulate the functionality of STRING_AGG and STRING_SPLIT.

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