Wednesday 11 December 2013

MSSQL - Simple GROUP_CONCAT

The simple and compact form of GROUP_CONCAT functionality in SQL Server.

Re-Creates table with test strings:
-- Re-Create and fill table with test strings
IF OBJECT_ID('dbo.TestTexts') IS NOT NULL
    DROP TABLE dbo.TestTexts;
GO
CREATE TABLE dbo.TestTexts (SomeText VARCHAR(20));
GO
INSERT INTO dbo.TestTexts
VALUES
    ('Aaa'),
    ('Bbb'),
    ('Ccc'),
    ('Ddd');
GO

Simple concatination of strings:
SELECT GROUP_CONCAT = '' + (
    SELECT '' + SomeText
    FROM dbo.TestTexts
    ORDER BY SomeText
    FOR XML PATH('')
)
Query Result:

GROUP_CONCAT
------------
AaaBbbCccDdd


Concatinates Strings with delimiter:
SELECT
    -- Remove First Comma
    GROUP_CONCAT = STUFF(
        (
            -- Concatinate Strings
            SELECT ',' + SomeText
            FROM dbo.TestTexts
            ORDER BY SomeText
            FOR XML PATH('')
        ),
        1,1,''
    );
GO    
Query Result:

GROUP_CONCAT
---------------
Aaa,Bbb,Ccc,Ddd