Concatenate multiple rows from multiple tables

Solution 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)