SQL Server Function to list the values as comma Separated Values.

This is one of the most common requirement and the most common bottleneck in stored procedure which are using cursors to attain this functionality.
If you are observing poor performance of your queries which are using cursors to produce a comma separated list then replace the code with below , it will improve the performance a lot.

Below function will produce the list of values as a comma seperated string for vname column in the table test.

GO

create table test (iid int, vname varchar(99))

GO

insert into test values (1,'A')
insert into test values (1,'B')
insert into test values (1,'C ')
insert into test values (2,'X')
insert into test values (2,NULL)
insert into test values (2,'Z')
insert into test values (3,NULL)
insert into test values (3,'DD')
GO


create function fn_tbl(@IID INT )
RETURNS TABLE

AS RETURN

(SELECT distinct B.IID, empnos = replace(replace((SELECT vname FROM test A
WHERE A.IID= B.IID ORDER BY A.IID FOR xml PATH ('')) ,'',''),'',',')
FROM TEST B
where B.IID = @IID
)

GO

SELECT distinct b.iid,left(empnos,len(empnos)-1) FROM TEST CROSS APPLY DBO.fn_tbl(iid) b

GO
SELECT * FROM DBO.fn_tbl(1)
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

GO

Reverse - From comma seperated list to columns

SELECT * FROM DBO.fn_comma ('A,B,C,D,E,-,Z,X,R')



ALTER  function fn_comma(@valueList varchar(MAX))
returns @RET TABLE (STR VARCHAR(MAX))
as
begin
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(MAX)
SET @valueList  = @valueList +','
set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
    --SELECT @pos, @len, @value /*this is here for debugging*/
       
    INSERT INTO @RET  SELECT @value
   
    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

RETURN
end