Function for comma seprated values from table

Generally we get the requirement that we need comma separated values on one column which we can combined with any other result set.


For example when we need to retrieve the all phone no of a customer or multiple line of address for the customer.

You can create a function in which you can pass a value on which you want all other values as comma separated. Once you create the function you can use in select statement to retrieve data in desired format.


These are the two ways to get string values in comma separated

Easy Ways for doing function
DECLARE @listStr VARCHAR(MAX)

SET @listStr = ''

SELECT @listStr = @listStr + NumberCols + ','

FROM NumberTable

SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)



DECLARE @listStr VARCHAR(MAX)


SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols


FROM NumberTable


SELECT @listStr