sql - 如何将这 2 个查询合并为 1？

我想添加一个逗号分隔的列，其中包含该产品的产品类别名称列表。这是我的数据模型:

我在此查询中获得了所有产品详细信息:

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

我只想使用此查询为每个产品添加 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,'');

它说 productId=1 但这是它需要连接到另一个查询的地方。
所以结果将是这样的:

DealerName  | Amount |date|product Name|csv
TestDealer1 | 105.25 |..  |ProdA       |CatA,CatC,CatF

我还为此创建了一个 sqlfiddle:http://sqlfiddle.com/#!6/d574e/4

最佳答案

试试这个 ..

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;

Working SQL FIDDLE

关于sql - 如何将这 2 个查询合并为 1？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22144764/