Feeds:
Posts

## 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.

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.

1. USING CURSOR
2. USING COALESCE
3. 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]
```

### 24 Responses

1. […] SQL SERVER – Create Comma Separated List From Table. […]

2. 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.

3. its very helpful for me.thanks a lot….

4. 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.

5. thanks alot for this post

6. Thanks 🙂

7. Thanks

8. if one column you have merged for more than one columns how will you merge

9. 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

11. Excellent…Thank you very much. I might have stopped at the coalesce..the use of stuff is exactly what I needed.

12. 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).

13. it works perfectly

14. Great Article. Helped me a lot. Thank you so much for posting such a nice article

15. Thanks. Excellent article

16. on August 7, 2015 at 2:23 pm | Reply chirag bhatt

nice !!

17. Its very helpful to me thanks you very much

18. thanks a lot it is very usefull

19. […] 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. […]

20. Just saved me a bunch of time, thanks.

21. on February 20, 2018 at 4:37 pm | Reply Srinivasulu Morapaka

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

Alhamdulillah,

23. […] 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 & […]

24. […] 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 […]