如何将这两个查询组合成1？
16lz
2021-01-22
I would like to add a commaseperated column with a list of product categorynames for that product. This is my datamodel:
我想添加一个commaseperated列，其中包含该产品的产品类别名称列表。这是我的数据模型：
I got all the productdetails in this query:
我在这个查询中得到了所有产品详细信息：
select d.name as 'Dealer Name',c.commissionamount as 'Commission Amount',c.createddate as 'Commission Created Date' ,p.name as 'Product Name'
from commission c
join dealer d
on d.dealerid=c.dealerid
join product p on c.productid=p.productid
join productcategorymapping pcm on p.ProductId=pcm.ProductId
join ProductCategory pc on pc.ProductCategoryId=pcm.ProductCategoryId
I just want to add the csv column for each product with this query , I just dont know how to join the 2:
我只想用这个查询为每个产品添加csv列，我只是不知道如何加入2：
select stuff((
SELECT ',' + pc.Name
FROM ProductCategory pc
join ProductCategoryMapping pcm on pcm.ProductCategoryId=pc.ProductCategoryId
join Product p1 on p1.ProductId=pcm.ProductId
and p1.ProductId=1
FOR XML PATH('')),1,1,'');
It says productId=1 but this is where it needs to be joined to the other query. So the result would be something like:
它表示productId = 1，但这是需要加入另一个查询的地方。所以结果会是这样的：
DealerName | Amount |date|product Name|csv
TestDealer1 | 105.25 |.. |ProdA |CatA,CatC,CatF
I also created a sqlfiddle for this:http://sqlfiddle.com/#!6/d574e/4
我还为此创建了一个sqlfiddle：http：//sqlfiddle.com/#！6 / d574e / 4
2 个解决方案
#1
1
Try this ..
尝试这个 ..
select
d.name AS [Dealer Name]
,c.commissionamount AS [Commission Amount]
,c.createddate AS [Commission Created Date]
,p.name AS [Product Name]
,stuff((SELECT ', ' + pcci.Name
FROM ProductCategory pcci
join ProductCategoryMapping pcmi
on pcmi.ProductCategoryId=pcci.ProductCategoryId
join Product p1i
on p1i.ProductId=pcmi.ProductId
WHERE p1i.ProductId = p.productid
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'')
AS [CSV]
from commission c join dealer d
on d.dealerid=c.dealerid
join product p
on c.productid=p.productid;