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