SQL Approach: Table Rows Concatenation

Whenever you need to concatenate strings stored inside a table column you have several alternatives.

Depending on the specific situation you may try one of the following approaches.

First lets create a test table filled with some sample data.

--test table

CREATE TABLE dbo.string_row

    row_id INT NOT NULL PRIMARY KEY,

    string VARCHAR(50),

    group_id INT NOT NULL

GO

--add two row groups leaving some row_id holes

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(1, 'AA', 1)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(2, 'BB', 1)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(4, 'CC', 1)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(8, 'EE', 2)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(12, 'FF', 2)

INSERT INTO dbo.string_row (row_id, string, group_id) VALUES(13, 'GG', 2)

When the situation is somewhat "static" we could use variables.

--1 variables
DECLARE @concatenation1 VARCHAR(MAX)

DECLARE @concatenation2 VARCHAR(MAX)

SET @concatenation1 = ''

SET @concatenation2 = ''

SELECT

    @concatenation1 = @concatenation1 +

        CASE group_id WHEN 1 THEN string ELSE '' END,

    @concatenation2 = @concatenation2 +

        CASE group_id WHEN 2 THEN string ELSE '' END

FROM dbo.string_row

ORDER BY row_id

SELECT @concatenation1 full_string, 1 group_id

UNION ALL

SELECT @concatenation2 full_string, 2 group_id

All other cases require one ore more queries.

Iteration using updates on a temporary table.

--2 iteration
CREATE TABLE #TMP

    row_id INT NOT NULL,

    full_string VARCHAR(MAX),

    group_id INT NOT NULL

INSERT INTO #TMP (row_id, full_string, group_id)

SELECT DISTINCT 0, '', group_id FROM dbo.string_row

WHILE @@ROWCOUNT > 0

BEGIN

    UPDATE T SET

        row_id = R.row_id,

        full_string = full_string + R.string

    FROM

        #TMP T

            INNER JOIN

        dbo.string_row R ON

            R.group_id = T.group_id AND

            R.row_id > T.row_id

            LEFT JOIN

        dbo.string_row R1 ON

            R1.group_id = T.group_id AND

            R1.row_id > R.row_id AND

            R1.row_id < T.row_id

    WHERE

        R1.row_id IS NULL

END

SELECT full_string, group_id FROM #TMP

Recursion using common table expressions.

--3 recursion
BEGIN

    WITH ORDROWS (new_row_id, full_string, group_id)

AS

        --remove row_id holes

        SELECT

            ROW_NUMBER()

                OVER (PARTITION BY group_id ORDER BY row_id) new_row_id,

            string full_string, group_id FROM dbo.string_row

),

    CROWS (new_row_id, full_string, group_id)

AS

        --get the last row in each group

        SELECT

            new_row_id, CONVERT(VARCHAR(MAX), full_string) full_string, group_id

        FROM

            ORDROWS R

        WHERE

            NOT EXISTS

                SELECT TOP 1 1

                FROM ORDROWS R1

                WHERE

                    R1.group_id = R.group_id AND

                    R1.new_row_id > R.new_row_id

        UNION ALL

        --add the previous row using the sequence obtained using ORDROWS CTE

        SELECT

            R.new_row_id,

            CONVERT(VARCHAR(MAX), R.full_string + C.full_string) full_string,

            R.group_id

        FROM

            ORDROWS R

                INNER JOIN

            CROWS C ON

                C.group_id = R.group_id AND

                C.new_row_id = R.new_row_id + 1

    SELECT full_string, group_id FROM CROWS WHERE new_row_id = 1

END

SQL Server xml capabilities.

--4 xml
SELECT

        SELECT

            R.string + ''

        FROM

            dbo.string_row R

        WHERE

            R.group_id = G.group_id

        ORDER BY R.row_id

        FOR XML PATH('')

    ) full_string,

    G.group_id

FROM

(SELECT DISTINCT group_id FROM dbo.string_row) G

You could download the complete script.

SQL Approach