How to combine multiple rows into one cell in a SQL Server table (T-SQL)

Introduction

This T-SQL script will demo how to combine multiple rows into one cell in a SQL Server table.  

Scenarios

As many people asked how to combine multiple rows into one cell in a SQL Server table, this script will provide some convenience.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from CombineRowsIntoOneRow.sql, paste it in a new query and run the script.

After the script finishes running, we’ll get the following figure:

Here are some code snippet for your reference: 

SELECT
    column1,
    STUFF((SELECT CAST(',' AS varchar(max)) + column2
            FROM @InitialTable  as b
            WHERE b.column1 = a.column1
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,'') AS column2
FROM
    (SELECT DISTINCT column1 FROM @InitialTable ) AS a;
GO

SELECTcolumn1, 
    STUFF((SELECTCAST(','ASvarchar(max)) + column2FROM@InitialTableasbWHEREb.column1 = a.column1FORXMLPATH(''), TYPE 
            ).value('.', 'varchar(max)' 
         ),1, 1,'') AScolumn2FROM 
    (SELECTDISTINCTcolumn1FROM@InitialTable ) ASa; 
GO

Prerequisites

SQL Server 2008 or higher version