How Can I Achieve String Aggregation in SQL Server Without an 'AGG' Function?

Grouped String Aggregation in SQL Server: Replace for 'AGG'

SQL Server lacks a built-in string aggregation function akin to 'AGG' in the query you provided. However, there are alternative approaches to concatenate row values into a grouped result.

One solution leverages the FOR XML and STUFF functions. The FOR XML function converts the result of the inner query into XML, which can then be processed with the STUFF function to remove XML tags and concatenate the strings. Here's an example:

SELECT *, 
(SELECT STUFF((
    SELECT ', ' + CarModel
    FROM CarModels model
    WHERE model.CarMakeID = make.CarMakeID
    FOR XML PATH('')
), 1, 1, '') as CarModels
FROM CarMakes make

Another approach utilizes the COALESCE and ROW_NUMBER functions. The COALESCE function concatenates non-null values, while the ROW_NUMBER function assigns unique row numbers within each group. The following query uses this approach:

SELECT CarMakeID, CarMake,
    COALESCE(
        (
            SELECT CarModel
            FROM CarModels model
            WHERE model.CarMakeID = make.CarMakeID
            AND ROW_NUMBER() OVER (PARTITION BY make.CarMakeID ORDER BY model.CarModelID) = 1
        ),
        '',
        COALESCE(
            (
                SELECT ', ' + CarModel
                FROM CarModels model
                WHERE model.CarMakeID = make.CarMakeID
                AND ROW_NUMBER() OVER (PARTITION BY make.CarMakeID ORDER BY model.CarModelID) > 1
            )
        )
    ) as CarModels
FROM CarMakes make

These approaches provide alternative methods to group and concatenate strings in SQL Server, enabling efficient and readable data aggregation.

The above is the detailed content of How Can I Achieve String Aggregation in SQL Server Without an 'AGG' Function?. For more information, please follow other related articles on the PHP Chinese website!