User Id :    Password :      New Member   Forgot Password
 
Get comma seperated values instead of resultset in sql server
Description Get comma seperated values instead of resultset in sql server   No. of Views     870
  Rating     0
Author Najmul Hoda   Posted On     12 Dec 2011
Tags SQL SERVER    

Sample Code   Download Code

 

Many tiimes we need comma seperated values as output from a sql query instead of a resultset. This can be acheived in many ways.

 

1. Using a cursor

2. Using a table variable or temp table (same as cursor)

3. Using a simple query (The easiest way)

 

Suppose we have a table Category and we would like to show list of category as comma seperated. The table is having the following categories.

 

SELECT Category FROM DBO.tblCategory ORDER BY Category

 

OUTPUT

 

Category
-----------------------------------
Office and Computer Desks
Office Workcenter Collections
Home Office Furniture
Office Chairs
File Cabinets
Reception Furniture
Bookcases
Office Tables
Conference Room Furniture
Entertainment Furniture
Computer Armoires
Credenzas
Storage Cabinets and Shelving
Office Panels
Breakroom Furniture
Office Security
Office Accessories
Kathy Ireland Office By Martin
Best Sellers

 

Now lets start with creating a comma seperated category list.

 

1. Using a cursor

 

 

DECLARE @CategoryList   VARCHAR(2000)
DECLARE @CategoryName     VARCHAR(50)

DECLARE myCursor CURSOR FOR
SELECT Category FROM DBO.tblCategory WHERE CategoryId<6 ORDER BY Category

SET @CategoryList=''

OPEN myCursor
FETCH NEXT FROM myCursor INTO @CategoryName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CategoryList +=  @CategoryName +','
	FETCH NEXT FROM myCursor INTO @CategoryName
END

CLOSE myCursor
DEALLOCATE myCursor

SELECT @CategoryList AS CategoryList
GO

 

2. Using a table variable or temp table (same as cursor)

 

 

DECLARE @CategoryList     VARCHAR(2000)
DECLARE @CategoryName     VARCHAR(50)
DECLARE @Count 		  INT
DECLARE @TotalCount 	  INT
DECLARE @myTbl            TABLE(ID AS INT IDENTITY(1,1), Category VARCHAR(50))

INSERT INTO @myTbl
SELECT Category FROM DBO.tblCategory WHERE CategoryId<6 ORDER BY Category

SET @CategoryList=''

SELECT @TotalCount=COUNT(0) FROM @myTbl
SET @COUNT=0

WHILE @TotalCount>=@COUNT
BEGIN
	SELECT @CategoryName=Category FROM @myTbl WHERE Id=@COUNT
	SET @CategoryList +=  @CategoryName +','
	
	SET @COUNT=COUNT+1	
END

SELECT @CategoryList AS CategoryList
GO

 

And finally the best and simplest way

 

3. Using a simple query (The easiest way)

 

DECLARE @CustomerIDs VARCHAR(8000)

SELECT @CustomerIDs = ISNULL(@CustomerIDs + ',', '') + [CustomerID]
FROM [dbo].[Customers]
WHERE CategoryId<6 ORDER BY Category

SELECT @CustomerIDs AS CustomerIDs
GO

 

On running all three statements you will find same output and the best way of doing this is obviously 3rd one.

 

OUTPUT

 

CategoryList
----------------------------------------------------------------------------------------------------------
File Cabinets,Home Office Furniture,Office and Computer Desks,Office Chairs,Office Workcenter Collections

 

Happy reading

 

About Author

About Author He is a .Net developer having 4+ years of experience. He loves writting technical article for .Net developers. Najmul Hoda
No Photo
 
Country India
Company BirlaSoft
Home Page www.dotnetlogix.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by