SQL - Concatenate Column Values from Multiple Rows into a Single Column

Use the below query to Concatenating Row Values

CREATE TABLE #PRODUCTS

(

    CategoryId        INT,

    ProductName        VARCHAR(50)

)

GO

INSERT INTO #PRODUCTS VALUES(1, 'ONE - ONE')

INSERT INTO #PRODUCTS VALUES(1, 'ONE - TWO')

INSERT INTO #PRODUCTS VALUES(2, 'TWO - ONE')

INSERT INTO #PRODUCTS VALUES(2, 'TWO - TWO')

GO

WITH Ranked ( CategoryId, rnk, ProductName ) 

AS 

(

    SELECT 

        CategoryId,

        ROW_NUMBER() OVER( PARTITION BY CategoryId ORDER BY CategoryId ),

        CAST( ProductName AS VARCHAR(8000) )

    FROM #PRODUCTS

),

AnchorRanked ( CategoryId, rnk, ProductName ) 

AS 

(

    SELECT 

        CategoryId, 

        rnk, 

        ProductName

    FROM Ranked

    WHERE rnk = 1 

),

RecurRanked ( CategoryId, rnk, ProductName )

AS

( 

    SELECT 

        CategoryId,

        rnk,

        ProductName

    FROM AnchorRanked

    UNION ALL

    SELECT 

        Ranked.CategoryId,

        Ranked.rnk,

        RecurRanked.ProductName + ', ' + Ranked.ProductName

    FROM Ranked

    JOIN RecurRanked ON Ranked.CategoryId = RecurRanked.CategoryId AND Ranked.rnk = RecurRanked.rnk + 1

)

SELECT CategoryId, MAX( ProductName )

FROM RecurRanked

GROUP BY CategoryId;

ACTUAL TABLE:

CategoryId  ProductName

----------- -----------

1           ONE - ONE

1           ONE - TWO

2           TWO - ONE

2           TWO - TWO

QUERY OUTPUT:

CategoryId  ProductName

----------- ----------------------

1           ONE - ONE, ONE - TWO

2           TWO - ONE, TWO - TWO

Original Source: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/