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 :
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
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
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.
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 »
SQL Server Portal 