create aggregate 1

[Kavius]

I know this is possible in PostgresSQL (sp?), MySQL and Red Hat's DB (whatever it is). Is it possible in SQL Server?

I have a query where, based on the grouping, I would like to concatinate the strings. For example, given:

Recipe  Ingredient
------- ----------
Soup    carrots
Soup    water
Soup    meat
Stew    carrots
Stew    water
Stew    meat
Stew    flour
Pudding pudding

I want to:

select recipe, concatinate(ingredient + ', ')
from   recipes
group by recipe

giving:

Recipe  Ingredient
------- ----------
Soup    carrots, water, meat
Stew    carrots, water, meat, flour
Pudding pudding,

How do I create concatinate? ________________________________________
Nature and Nature's laws lay hid in night
God said "Let Newton be", and all was light
~Alexander Pope~

 

[Gkannan]

If you are mentioning two three columns in select clause, then you have to mention those columns in group by clause also.

If you mentioned recipes, Ingredient in group by clause, then you can't get what ever you expecting.

 

[Kavius]

Sorry, wasn't clear.

I'm not having any problems with group clauses, and I understand how agregate functions work. I want to create a new aggregate function that is not in SQLServer.

Is this possible in SQLServer?
________________________________________
Nature and Nature's laws lay hid in night
God said "Let Newton be", and all was light
~Alexander Pope~

 

[r937]

sounds like you want to create a UDF -- user defined function

i've never written one because i've never needed one

a UDF is, apparently, just a stored proc that you can call using function syntax

see thread183-269745 or do a search for UDF in this forum

rudy

 

[tlbroadbent]

See faq183-2146. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Kavius]

tlbroadbent,

Thank-you. I was afraid of something like that, I was just hoping to make an agregate to avoid nasty cursors and storedprocedures. I figured it would make the code a little more reusable.

As best I can tell, it is not possible to create aggregate functions in SQL Server.

Again...Thanks.
________________________________________
Nature and Nature's laws lay hid in night
God said "Let Newton be", and all was light
~Alexander Pope~

 

[tlbroadbent]

I hadn't considered it, but you could create a function to concatenate in SQL 2000. If I get a chance, I may work on it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Kavius]

You could do it fairly easily with parameters. Basically, you would be passing your group by clause into the function. Nasty, but it would work.

The nasty part are the items that become variable:[ul]
[li]table[/li]
[li]column[/li]
[li]grouping columns[/li]
[/ul]

________________________________________
Nature and Nature's laws lay hid in night
God said "Let Newton be", and all was light
~Alexander Pope~