Thursday, December 10, 2015

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

SET @listStr = ''

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

FROM NumberTable

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


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

FROM NumberTable

SELECT @listStr

No comments:

Post a Comment