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]