SQL SERVER: Generate Comma Separated List with SELECT statement

Scenario:

Create a table and insert some data like:

create table tblProductCategory (categoryId int,categoryName varchar(max))

insert into tblProductCategory values (1,'Computer')

insert into tblProductCategory values (2,'Mobile')

Create next table and insert some data like:

create table tblProductDetails (id Int, categoryID int, productName varchar(max))

insert into tblProductDetails values (1,1,'Intel')

insert into tblProductDetails values (2,1,'CMD')

insert into tblProductDetails values (3,1,'Samsung')

insert into tblProductDetails values (4,1,'LG')

insert into tblProductDetails values (5,2,'Ecron')

insert into tblProductDetails values (6,2,'iPhone')

insert into tblProductDetails values (7,2,'Nokia')

Method (1) :

select C.categoryID,C.categoryName,    (Stuff((SELECT ', ' + productName AS [text()]

            FROM (SELECT DISTINCT PD.productName FROM tblProductDetails PD where PD.categoryID= C.categoryId) x       

                For XML PATH ('')),1,1,''))as ProductDetails

       from tblProductCategory C

Method (2) :

create function dbo.commaSeparatedString (@categoryID int)

returns nvarchar(max)

as

begin

    declare @return nvarchar(max)=''

        SELECT @return = Stuff((SELECT ', ' + productName

        FROM (SELECT DISTINCT productName FROM tblProductDetails where categoryID=@categoryID) x       

            For XML PATH ('')),1,1,'')

    return @return

end

select C.categoryID,C.categoryName ,dbo.commaSeparatedString(C.categoryID)as ProductDetails from tblProductCategory C

Method (3) :

SELECT categoryId,categoryName,SUBSTRING(( SELECT ( ', ' + productName)FROM tblProductDetails t2  WHERE t1.categoryId = t2.categoryId  ORDER BY t1.categoryId, t2.categoryId  FOR XML PATH('')), 3, 1000)as ProductDetails

FROM tblProductCategory t1

Output :

CategoryID	CategoryName	ProductDetails
1	Computer	CMD, Intel, LG, Samsung
2	Mobile	Ecron, iPhone, Nokia

Please make comments, if this helps you in any way