Get comma seperated values instead of resultset in sql server
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