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

Aaron Bertrand · Accepted Answer · 2022-01-24T13:15:30.807

3

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)

edited Jan 24 '22 at 13:15

answered May 01 '12 at 22:54

Aaron Bertrand

272,866
37
466
490

1

Yeah I'm still finding CLR is roadblocked by two things: (a) pointy-haired bosses thinking it is dangerous, and (b) the very real complexity in deploying code. Not just initially but when you need to make changes it can become a very tedious exercise... – Aaron Bertrand May 01 '12 at 23:12
@AaronBertrand I'm blown away that I got the answer to this puzzle so quickly. I was able to move this into a function with only one small adjustment (had to move the where logic see [here](https://gist.github.com/2571968)). I'll have to go home and study this a bit more to see whats _really_ going on, but thank you again for your prompt response. – AJ Tatum May 01 '12 at 23:19
@AaronBertrand agree with that! But don't you think (assuming you bypass (a) and want to face (b)), using a CLR function it's the appropiate way? I mean, I find the query is a bit complex and counter-intuitive for such a simple requirement. – Chopin May 01 '12 at 23:24
I suspect the query looks more complex than a CLR deployment for someone who comes from an OO / .NET background. For me, I wrote that query in about 8 minutes. I would still be deploying DLLs had I chosen to do it in CLR. It is also the same complexity when I move the code to a new server (the query is much more portable) and as I said it is even worse if I have to make a tweak to the query (like I would have had to do in this case because I mislocated the where clause). I'm not saying CLR is bad, but I think your view of which is more complex/counter-intuitive is quite subjective. – Aaron Bertrand May 01 '12 at 23:26
@AaronBertrand agree with that too! and yes, my opinion could be a bit subjective by my background in .NET, but also bear in mind, if someone that is not as skilled as you needs to tweak that query, it could become a headache! – Chopin May 01 '12 at 23:36
If someone is only moderately skilled in .NET and only moderately skilled in T-SQL, which solution do you think would be more effort to maintain? Be honest. – Aaron Bertrand May 01 '12 at 23:37
I think that depends on the changes to be made. – Chopin May 01 '12 at 23:57

YS. · Answer 2 · 2012-05-01T22:54:10.953

0

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

edited May 01 '12 at 22:54

answered May 01 '12 at 22:49

YS.

1,808
1
19
33

Thank you for your quick response. While this wasn't the solution I went with, I thank you for showing me something new. I wasn't even aware of that possibility. – AJ Tatum May 01 '12 at 23:20

Concatenate multiple rows from multiple tables

2 Answers2

Linked