Concatenate multiple rows from multiple tables

Question

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.

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.

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)

However, I'm trying to get it to look like:

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

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

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,

Andrew

Answer 1

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 = 2, 
        @SurgeonID  int = 1, 
        @LocationID int = 1;

;WITH x AS 
(
  SELECT CommissionPercent, Categories = STUFF((SELECT N', ' 
      + 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(N'./text()[1]', N'nvarchar(max)'), 1, 2, N'') 
 FROM #tCommissions AS com
 WHERE SalesRepID = @SalesRepID
   AND SurgeonID  = @SurgeonID
   AND LocationID = @LocationID
),
y AS
(
  SELECT s = RTRIM(CommissionPercent) + N' (' + Categories + N')' 
  FROM x GROUP BY CommissionPercent, Categories
)
SELECT Result = STUFF((SELECT N', ' + s FROM y 
  FOR XML PATH, 
  TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, N'');

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)