T-SQL to Convert Rows into single Column

Many times we need to convert column values into rows. There are two scenarios here. First one is to concatenate all the values in column. And second one is to concatenate all the values in column along with another grouping column.

Consider you have below table

CREATE TABLE [dbo].[Tbl_Employee]

   (

     Id INT,

     Name VARCHAR(255)

   )

   GO

Insert some data in above table.

INSERT INTO [dbo].[Tbl_Employee ] 

      (Id, Name)

   SELECT 1, 'Amit' UNION ALL   

   SELECT 1, 'Bhanu' UNION ALL

   SELECT 1, 'Chandu' UNION ALL

   SELECT 1, 'Dhanush' UNION ALL

   SELECT 2, 'Ellesh' UNION ALL

   SELECT 2, 'Fair' UNION ALL

   SELECT 2, 'Govind' UNION ALL

   SELECT 3, 'Header' UNION ALL

   SELECT 3, 'Indu' UNION ALL

   SELECT 4, 'Janu' 

   GO

So you have following data in [dbo].[MyTable]

SELECT *  FROM [Tbl_Employee]

First – Concatenate all the values in column “Name”

   DECLARE @StrSQL VARCHAR(8000)

   SELECT @StrSQL = COALESCE(@StrSQL + ',', '') + Name

   FROM [dbo].[Tbl_Employee ]

   SELECT @StrSQL

   GO

   Name 
   ------------------------
   Amit,Bhanu,Chandu,Dhanush,Ellesh,Fair,Govind,Header,Indu,Janu

Second – Concatenate all the values in column “Name” grouping by column “Id”

   SELECT  

      Id,

      STUFF( (

                  SELECT

                  ',' + Name AS 'text()'

                  FROM [dbo].[Tbl_Employee ] t2

                  WHERE t2.id = t1.id

                  FOR XML PATH('')

               )

               ,1

               ,1

               ,''

             ) AS Name

   FROM [dbo].[Tbl_Employee ] t1

   GROUP BY Id

   GO