Transform Multiple Rows Into One Comma Separated Value In SQL Server

Introduction
In this article we will learn How to convert multiple rows into one  comma separated value? Convert many rows into a single text string with any separator. 

Previous Updates

In previous articles we have learnt Best basic C# interview questions. what is TypeScript and How to Install this on VS2015 .AngularJS basics, What is Cursor in sql and use of cursor. 


Description
Many of use comes across this problem while working with tables and data. Here i explain this in three different methods using them you can easily convert or transform your multiple row data into single row data.

CREATE TABLE MytemData( ID INT IDENTITY(1,1), Code VARCHAR(50)) Insert into MyTempData values ('AC') Insert into MyTempData values ('95') Insert into MyTempData values ('79') Insert into MyTempData values ('BL') Insert into MyTempData values ('CDC') Insert into MyTempData values ('IIA') Insert into MyTempData values ('MNC')

1st Method :-

DECLARE @temp VARCHAR(MAX) SET @temp = (SELECT ', ' + cast(Code as varchar)                       FROM [dbo].[MyTempData]                       ORDER BY Code                      FOR XML PATH('')) SELECT SUBSTRING(@temp, 2, 200000) AS Code

2nd Method :-

DECLARE @temp VARCHAR(MAX) SET @temp = '' SELECT @temp = @temp + Code + ', ' FROM [dbo].[MyTempData] SELECT SUBSTRING(@temp, 0, LEN(@temp))

3rd Method :-

DECLARE @temp VARCHAR(MAX) SELECT @temp = COALESCE(@temp+', ' ,'') + Code FROM [dbo].[MyTempData] SELECT @temp

And after performing all of these three methods always your output looks like this.