user defined aggregate functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

user defined aggregate functions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-08 : 08:22:08

Richard writes "I have a need to create an aggregation fuinction that currently dose not exist in SQL Server 2000.

I want this function to be able to concatinate the grouped values of a string column.

i.e.
create table table1 (code varchar(2),desc varchar(40))
insert into table1(code,desc) value ('1','here ')
insert into table1(code,desc) value ('1','there ')
insert into table1(code,desc) value ('1','everywhere')
insert into table1(code,desc) value ('2','this ')
insert into table1(code,desc) value ('2','is ')
insert into table1(code,desc) value ('2','a ')
insert into table1(code,desc) value ('2','test ')

if selected normally this would result in

code desc
----- ----------------------------
1 here
1 there
1 everywhere
2 this
2 is
2 a
2 test

I want to create the function CONCAT_LIST to be used as follows.

Select code, CONCAT_LIST(desc) as desc
FROM table1
GROUP BY code

the resut should be

code desc
----- -----------
1 here there everywhere
2 this is a test

in triggers there are INSERT and DELETE tables. is there something simmilar for group by's ?

help "

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 08:56:00

create function CONCAT_LIST
(@id as varchar(2))
returns varchar(1000)
AS
begin
declare @csv varchar(1000)
select @csv = coalesce(@csv+' ','') + desc
from table1
where code = @id
return @csv
end
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources