SQL SERVER – Create Comma Separated List From Table

« SQL SERVER – Proper Case User-Defined Function

SQL SERVER – Function to Extract Numbers From String »

SQL SERVER – Create Comma Separated List From Table

December 18, 2012 by Muhammad Imran

I was preparing a statistical report and was stuck in one place where I needed to convert certain rows to comma separated values and put into a single row.

Lets say I have multiple currencies in a table and I would like to display in a single comma separated rows.

I found few options to resolve this.

USING CURSOR
USING COALESCE
USING STUFF

Let me create a simple example to explain these methods in detail.

Create Table tbl_Currency
(
[Currency] varchar(50)
)
GO
Insert into tbl_Currency Values ('US dollar')
Insert into tbl_Currency Values ('European euro')
Insert into tbl_Currency Values ('Indian rupee')
Insert into tbl_Currency Values ('Pakistani rupee')
Insert into tbl_Currency Values ('Philippine peso')
Insert into tbl_Currency Values ('UAE dirham')
Insert into tbl_Currency Values ('British pound')

Let me explain these methods in detail :

USING CURSOR:

This is a simple way for development but performance wise it will cost a lot for your application.

Given below is a CURSOR example :

CREATE PROCEDURE Cursor_Example1_Proc
AS
DECLARE @Currency varchar(Max)
DECLARE @Consolidated_Currency varchar(Max)
DECLARE Cur_Cursor CURSOR FOR
SELECT [Currency] FROM tbl_Currency

OPEN Cur_Cursor

FETCH NEXT FROM Cur_Cursor INTO @Currency

WHILE @@FETCH_STATUS = 0
BEGIN
Set @Consolidated_Currency =ISNULL(@Consolidated_Currency,'')
+ ISNULL(@Currency + ', ','')

FETCH NEXT FROM Cur_Cursor INTO  @Currency
END
Select Left(@Consolidated_Currency,LEN(@Consolidated_Currency)-1) as [Currency]

CLOSE Cur_Cursor
DEALLOCATE Cur_Cursor
GO
EXEC Cursor_Example1_Proc
GO

USING COALESCE:

We can also achieve the same using COALESCE function. Given below is an example.

Declare @Currency varchar(Max)
Set @Currency=''
Select @Currency=@Currency + Coalesce([Currency]+ ', ','') from tbl_Currency
Select Left(@Currency,LEN(@Currency)-1) as [Currency]
GO

USING STUFF:

This is the recommended / best way to do this because you can achieve the same result without any variable and less lines of code. Given below is an example.

SELECT STUFF((SELECT ', ' + [Currency] FROM tbl_Currency FOR XML PATH('')),1,1,'') as [Currency]
GO

RESULT :
The result of all of the above methods is the same. Given below is the result.

REAL WORLD EXAMPLE :

As mentioned above, I was working on a report where multiple Bank A/C # should be displayed next to the customer name like a comma separated field.

Let me create an example to explain this :

Create Table tbl_Customer_Bank_Details
(
[Customer ID] int,
[Customer Name] varchar(50),
[Bank Account No] varchar(50)
)
GO
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A001')
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A002')
Insert into tbl_Customer_Bank_Details Values (1,'Imran','A003')
Insert into tbl_Customer_Bank_Details Values (2,'Bob','B001')
Insert into tbl_Customer_Bank_Details Values (2,'Bob','B002')
GO
Select * from tbl_Customer_Bank_Details
--RESULT

Lets combine the account number (comma separated values) with respect to the customer.

SELECT [Customer ID], [Customer Name]
, STUFF((SELECT ', ' + A.[Bank Account No] FROM tbl_Customer_Bank_Details A
Where A.[Customer ID]=B.[Customer ID] FOR XML PATH('')),1,1,'') As [Bank Accounts]
From tbl_Customer_Bank_Details B
Group By [Customer ID], [Customer Name]

Posted in Solutions, SQL SERVER | Tagged Comma Seperated List, Convert column to rows, Merge or Combine Multiple Rows Records to Single Column Record with Comma delimiters, raresql, SQL, SQL Server, SQL SERVER – Create Comma Separated List From Table | 24 Comments

24 Responses

on December 27, 2012 at 2:28 pm | Reply SQL SERVER – Create Comma Separated List From Table « Orientprogrammer's

[…] SQL SERVER – Create Comma Separated List From Table. […]
on June 28, 2013 at 8:43 am | Reply N.V.Krishna

I did a rigorous search to obtain examples like this. But no luck any of the reputated SQL blogs/communities .

Finally fixed my mind to go with SP with cursor but after seeing this concatenate aggragation mechanism with STUFF in the afore mentioned example which comes in very handy and minimal code pain.

Thanks much for this cutest example Imran.
on July 2, 2013 at 12:09 pm | Reply jatin

its very helpful for me.thanks a lot….
on July 19, 2013 at 6:43 pm | Reply Mangesh

I did lots of head scratching to create a List but was not able to. Finally landed on this blog and found the solution. Thanks for posting this blog with all options to create List.
on May 21, 2014 at 12:47 pm | Reply Mohideen

thanks alot for this post
on June 6, 2014 at 3:39 pm | Reply Dara

Thanks 🙂
on June 9, 2014 at 7:29 pm | Reply Sravan

Thanks
on July 18, 2014 at 1:28 pm | Reply Kavin

if one column you have merged for more than one columns how will you merge
on January 8, 2015 at 9:27 pm | Reply desperadomar

Another method I found from here (http://www.sqlbook.com/SQL/Create-comma-delimited-list-27.aspx)

Declare @Currency varchar(Max)
Set @Currency=”
Select @Currency=@Currency + Currency+’,’ from tbl_Currency
SET @Currency= SUBSTRING(@Currency, 1, Len(@Currency) – 1)
select @currency
on April 30, 2015 at 12:15 pm | Reply Madhava

Thanks. This article resolved my issue
on April 30, 2015 at 7:07 pm | Reply Greg

Excellent…Thank you very much. I might have stopped at the coalesce..the use of stuff is exactly what I needed.
on May 27, 2015 at 11:12 pm | Reply AndyDBA

Yes, FOR XML has been widely accepted for years as the fastest T-SQL way to do this. Be careful though if your data contains reserved XML characters (,&,etc) because they will be “escaped” in your result.

As a side note, your heading says “USING STUFF”, but the concatenation is actually done by FOR XML PATH(”). STUFF is just used to delete the leading 2 characters (comma space).
on June 10, 2015 at 5:30 pm | Reply loga

it works perfectly
on July 3, 2015 at 8:36 pm | Reply Nimisha

Great Article. Helped me a lot. Thank you so much for posting such a nice article
on August 6, 2015 at 12:18 pm | Reply Malti bajaj

Thanks. Excellent article
on August 7, 2015 at 2:23 pm | Reply chirag bhatt

nice !!
on January 15, 2016 at 3:04 pm | Reply santhosh

Its very helpful to me thanks you very much
on January 20, 2016 at 11:16 am | Reply Yogesh

thanks a lot it is very usefull
on April 29, 2016 at 12:30 pm | Reply How To Create Xml From Table In Sql Server | Insurance-Golds

[…] SQL SERVER – Create Comma Separated List From Table – I was preparing a statistical report and was stuck in one place where I needed to convert certain rows to comma separated values and put into a single row. […]
on October 20, 2016 at 6:53 pm | Reply Matt Owen

Just saved me a bunch of time, thanks.
on February 20, 2018 at 4:37 pm | Reply Srinivasulu Morapaka

it’s really good and help full article
on October 3, 2019 at 7:32 pm | Reply Daniel Adeniji

Muhammad :-

Every time I think of a comma list only one name comes to mind. And, that is RareSQL.

Alhamdulillah,

Daniel Adeniji
on December 1, 2022 at 6:00 am | Reply SQL Server – STRING_AGG() Function | SQL Server Portal

[…] been easier in earlier (before SQL Server 2017) version of SQL Server. I have written a detailed article back in 2012 regarding this issue, where I used CURSOR, COALESCE() function, STUFF() function & […]
on March 23, 2023 at 6:00 am | Reply SQL SERVER 2022 – Multiple ways to remove the last character in a string | SQL Server Portal

[…] in a string has never been easier before SQL Server 2022 version. I had written a detailed article back in 2013 regarding this issue, where I used, STUFF(), LEN() functions […]