How do I aggregate on a text field?

Question

I want to roll up on ProductID and append all the ingredients together in the rolled-up row for that ProductID. ProductID is the "GROUP BY", what operator can be used on the Ingredient field.

This is not a data warehouse. I know STRING_AGG is available in SQL Server 2017 and I don't have that either, I have SQL Server 2012.

This doesn't make much sense. You have two columns in the output, you are grouping by both of them. This is the same thing as distinct. You can always use MIN or MAX on varchar data but in this case it makes no difference. And be careful....looks like you are nesting views. This is the work of the devil. — Sean Lange, Feb 13 '18 at 22:51
I don't want to GROUP BY on the Ingredient, I want an expression that appends then together for each product group. — nicomp, Feb 13 '18 at 22:52
My first suggestion would be to close that silly wizard so you can write your own sql. You can't do this with an aggregate. The lookup how to generate a delimited list with t-sql. It has been asked and answered around here hundreds of times. — Sean Lange, Feb 13 '18 at 22:53
Here is one such example. https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Sean Lange, Feb 13 '18 at 22:54
@nicomp you are looking for `STUFF` function possibly https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql — Vidmantas Blazevicius, Feb 13 '18 at 23:00

score 1 · Answer 1 · answered Feb 14 '18 at 00:09

Taking a stab at what you might be looking for using the stuff for xml example..

;with mycte as (
select '100' as product, 'a' as ingredient
union all
select '100' as product, 'b' as ingredient
union all
select '100' as product, 'c' as ingredient
union all
select '200' as product, 'd' as ingredient
union all
select '200' as product, 'e' as ingredient
union all
select '200' as product, 'f' as ingredient
)

select distinct
 product ,
stuff((select ','+ingredient
from mycte mycte1
where mycte1.product=mycte.product
for xml path('')),1,1,'') as ingredient_list

 from mycte

RESULT SET

product  ingredient_list
100      a,b,c
200      d,e,f

score 0 · Answer 2 · answered Feb 14 '18 at 00:28

Another alternative, using a loop in a function:

create function [dbo].[GetIngredients](@ProductID int)
returns varchar(max)
begin
declare @result varchar(max), @ingredient varchar(100)
set @ingredient=''
set @result=''
while exists(select * from vBobbyTables where ProductID=@ProductID 
  and ingredient>@ingredient)
begin
  set @ingredient=(select min(ingredient) from vBobbyTables where ProductID=@ProductID 
    and Ingredient> @ingredient)
  if @result<>''
    set @result=@result+','
  set @result=@result+@ingredient
end
return @result
end

Then call it in your select:

select productid, dbo.getingredients(productid)
from vBobbyTables
group by productid

How do I aggregate on a text field?

2 Answers2