SQL Server Portal

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]

Read Full Post »