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
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, '');
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)
I bumped into similar problem before - and the only way I could resolve this (without using cursors), is by creating a CLR aggregate function. Here's an example in C# (and in VB): http://technet.microsoft.com/en-us/library/ms131056(v=SQL.90).aspx
I believe it just does what you need: concatenation.
Combining your example and the CLR, to achieve what you want - the SQL would look like:
SELECT
c.CommissionPercent
, dbo.MyAgg(cat.Category)
FROM #tCommissions AS c
JOIN #tCategories AS cat ON c.CategoryID = cat.CategoryID
group by c.CommissionPercent
