Concatenate multiple row values in one string

Objective:
Here is another one. Suppose you have one table with two columns, ID & Name. However, you need to select values from the Name column and separate the values with a comma like this:
Name1, Name2, Name3…

Here is an example:

You have table with two fields:


You want the result set to look like the following:

Solution:
The solution is very simple, as is always :)

1. Create one User Defined Function (UDF) that will accept the comma separated ID and return the comma separated name.

CREATE FUNCTION Func_GetNameFromCommaSepId(

@Id VARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

DECLARE @Name NVARCHAR(MAX)

SELECT @Name = COALESCE(@Name+ ', ', '')+ Name From tblMaster where Convert(varchar(5),Id) in(Select DataItem From dbo.Func_SplitString(@ID,','))

RETURN @Name

END

2. Now use this UDF as your query.

select dbo.Func_GetNameFromCommaSepId('1,2,3,4,5,6,7')as Name

Your result set will look like this: