Click here to Skip to main content
15,867,986 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How do i use the coalesce function to generate a comma separated series?
For example, 1,4,9,16,25,36....., 400.
Posted

try this

SQL
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Cast(empid as  varchar(10))
FROM table1
SELECT @listStr

Here empid is my integer column
and table1 is my table


For your scenario, try this

SQL
declare @count as int=1
DECLARE @listStr VARCHAR(MAX)
while @count<=20
BEGIN
SELECT @listStr = COALESCE(@listStr+',' ,'') + Cast(SQUARE(@count) as varchar(10))
set @count=@count+1
END
select @listStr


output:
1,4,9,16,25,36,49,64,81,100,121,144,169,196,225,256,289,324,361,400
 
Share this answer
 
v2
Comments
bolshie6 27-Sep-12 5:53am    
Thanks a lot. It works
The Doer 27-Sep-12 6:01am    
my 5 for lucid explanation..! dont know why ppl forget to mark their answer when it helps..!
bolshie6 27-Sep-12 6:05am    
Can u explain me how actually COALESCE helps doing this?
Santhosh Kumar Jayaraman 27-Sep-12 6:06am    
You need to check this
http://msdn.microsoft.com/en-us/library/aa258244(v=sql.80).aspx
bolshie6 27-Sep-12 6:12am    
Okey, got it. I actually thought COALESCE returns all the NON NULL values. But got it now, it returns only the first NON NULL value. Thanks again.
If you want to find a way to get values from one field in the database table, not in rows but in one row using comma separator, you can use STUFF() function to.

SQL
DECLARE @cols NVARCHAR(2000)

SET @cols = STUFF((SELECT DISTINCT ',' + T.[FieldName]
					FROM [DatabaseName].[dbo].[TableName] AS T
					ORDER BY ',' + T.[FieldName]
			FOR XML PATH('')),1,2,'') + ''

SELECT @cols AS [CommaSepratedData]
 
Share this answer
 
SQL
select substring(Result,0,Len(Result)) as List from
    (
        select 
		(  
			select convert(varchar,ColNm)+ ',' as [text()] from TblNm order by ColNm for xml path('')
		) as Result
    )
as a

Happy Coding!
:)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900