Tuesday, 28 August 2012

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 


No comments:

Post a Comment