连接多个表中的多行 [英] Concatenate multiple rows from multiple tables

问题描述

我已经查看了许多 其他帖子在这里并且已经非常熟悉Coalesce 函数，但我一直无法弄清楚如何执行此特定任务.

I've reviewed many other posts on here and have become pretty familiar with the Coalesce function, but I haven't been able to figure out how to do this specific task.

所以，我有一个佣金表和一个类别表.我创建了一个 gist here 所以你可以看到确切的带有一些示例数据的数据结构.基本上，佣金表有一个 SalesRepID、LocationID、CategoryID、SurgeonID 和 CommissionPercent 列.

So, I have a Commissions table and a Categories table. I've created a gist here so you can see the exact data structure with some example data. Basically, the Commission table has a SalesRepID, LocationID, CategoryID, SurgeonID, and CommissionPercent column.

使用 Coalesce 函数，我已经能够得到通过传入 SalesRepID、LocationID 和 SurgeonID，类似这样的事情:

Using a Coalesce function, I've been able to get something like this by passing in the SalesRepID, LocationID, and SurgeonID:

.05 (Shirts), .05 (Shoes), .05 (Dresses), .10 (Hats), .15 (Pants)

但是，我试图让它看起来像:

.05 (Shirts, Shoes, Dresses), .10 (Hats), .15 (Pants)

我确实用 STUFF 尝试了几次，但始终没有得到我想要的结果.

I did try it a few times with STUFF, but I never got the result that I'm looking for.

这让我想问，这在 MsSQL 2008 R2 中是否可行?如果是，我将不胜感激任何帮助获得我正在寻找的结果.

Which leads me to ask if this is even possible in MsSQL 2008 R2? If it is, any help in getting the result I'm looking for would be greatly appreciated.

非常感谢您的宝贵时间&能量，

Thank you very much for your time & energy,

安德鲁

推荐答案

感谢您提供的要点！比拔牙获取模式和数据要好得多.:-) 如果您将此插入到您的要点查询中，您应该会看到您所追求的结果(好吧，非常接近 - 见下文).

Thank you for the gist! So much better than pulling teeth to get schema and data. :-) If you plug this in to your gist query you should see the results you're after (well, very close - see below).

DECLARE @SalesRepID INT, @SurgeonID INT, @LocationID INT;
SELECT @SalesRepID = 2, @SurgeonID = 1, @LocationID = 1;

;WITH x AS 
(
  SELECT CommissionPercent, Categories = STUFF((SELECT ', ' 
      + tCat.Category FROM #tCategories AS tCat 
      INNER JOIN #tCommissions AS tCom 
      ON tCat.CategoryID = tCom.CategoryID
      WHERE tCom.CommissionPercent = com.CommissionPercent
      FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
 FROM #tCommissions AS com
 WHERE SalesRepID = @SalesRepID
 AND SurgeonID = @SurgeonID
 AND LocationID = @LocationID
),
y AS
(
  SELECT s = RTRIM(CommissionPercent) + ' (' + Categories + ')' 
  FROM x GROUP BY CommissionPercent, Categories
)
SELECT Result = STUFF((SELECT ', ' + s FROM y 
  FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

结果与您要求的略有不同，但您可以通过在提取 CommissionPercent 时应用字符串格式来解决该问题.

The result is slightly different than you asked for, but you could fix that by applying string formatting when pulling CommissionPercent.

Result
--------------------------------------------------------
0.05 (Shirts, Shoes, Dresses), 0.10 (Hats), 0.15 (Pants)

这篇关于连接多个表中的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！