Aggregate functions

Hi all,
Is there any way to create my own aggregate functions on SQl server 2000 ?
I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
any aquivalent for SQL server ?
Thanx in advance for your replies.No, though there may be a way in SQL Server 2005. What aggregate do you
need? Some aggregates that aren't built in can still be implemented in
the current version.
Steve Kass
Drew University
imparfait wrote:
>Hi all,
>Is there any way to create my own aggregate functions on SQl server 2000 ?
>I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
>any aquivalent for SQL server ?
>
>Thanx in advance for your replies.
>
>
>|||Hi Steve,
Thank you for ur reply.
I was surprized by the poorness of aggregation function number ( about a
dozen ).
My aim is to return for example the concatenation of some field on the
grouped by set.
here's a simplified details example:
ID | NAME
1 | foo1
2 | foo1
3 | foo2
4 | foo2
and I want to return the concatenantion of the IDs having the same NAME
something like :
select my_own_aggregation_function(ID,','), NAME from my_table group by NAME
would return :
1,2 | foo1
3,4 | foo2
the second argument of my_own_aggregation_function is a separator ( in this
example it's a "," )
Of course I can solve this another way but creating my own aggregation
function seems to me the most evident thing to think about as it avoids to
make an additional query.
Note : I am working on a huge database ( millions of lines...)
Thanx again for your answer.
"Steve Kass" <skass@.drew.edu> a écrit dans le message de
news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...
> No, though there may be a way in SQL Server 2005. What aggregate do you
> need? Some aggregates that aren't built in can still be implemented in
> the current version.
> Steve Kass
> Drew University
> imparfait wrote:
> >Hi all,
> >Is there any way to create my own aggregate functions on SQl server 2000
?
> >
> >I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
> >any aquivalent for SQL server ?
> >
> >
> >Thanx in advance for your replies.
> >
> >
> >
> >
> >
>|||There is no feature of SQL Server specifically for this requirement,
though user-defined aggregates may be available in SQL Server 2005. It
might be well worth looking at a third-party tool such as the one at
http://www.rac4sql.net, or checking the capabilities of report writers.
Nonetheless, there are some SQL options, and here are three. They
assume that the items to be aggregated are distinct, and they all put
those items into lists in alphanumeric order.
1. If you know the maximum number of items that will appear for a
particular NAME value, you can do this, which is very efficient if there
is an index on (NAME, ID), or perhaps even on just (NAME). [Example
uses Northwind's Orders table, and itentionally shows how it can miss
too-long lists]:
use Northwind
go
select
t.CustomerID,
max(case rnk when 1 then OrderID end) +
coalesce(','+max(case rnk when 2 then OrderID end),'') +
coalesce(','+max(case rnk when 3 then OrderID end),'') +
coalesce(','+max(case rnk when 4 then OrderID end),'') +
coalesce(','+max(case rnk when 5 then OrderID end),'') +
coalesce(','+max(case rnk when 6 then OrderID end),'') +
coalesce(','+max(case rnk when 7 then OrderID end),'') +
coalesce(','+max(case rnk when 8 then OrderID end),'') +
coalesce(','+max(case rnk when 9 then OrderID end),'') +
coalesce(','+max(case rnk when 10 then OrderID end),'') +
coalesce(','+max(case rnk when 11 then OrderID end),'')
from (
select
t1.CustomerID,
cast(t1.OrderID as char(5)) as OrderID,
count(t2.OrderID) as rnk
from Orders t1, Orders t2
where t1.CustomerID = t2.CustomerID
and t1.OrderID <= t2.OrderID
group by t1.CustomerID, t1.OrderID
) t
group by CustomerID
2. If you don't know the number of items, but there is still a useful
index, a cursor is a reasonable choice:
CREATE TABLE Result (
KeyCol char(5),
List varchar(2000)
)
DECLARE C CURSOR FAST_FORWARD FOR
SELECT CustomerID as KeyCol, rtrim(OrderID) as Tag
FROM Northwind..Orders
ORDER BY 1,2
OPEN C
BEGIN TRAN
DECLARE @.currK varchar(30), @.K varchar(30),
@.nextT varchar(80), @.T varchar(2000)
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 BEGIN
SET @.T = @.nextT
SET @.currK = @.K
FETCH NEXT FROM C INTO @.K, @.nextT
WHILE @.@.fetch_status = 0 AND @.K = @.currK BEGIN
SET @.T = @.T + ',' + @.nextT
FETCH NEXT FROM C INTO @.K, @.nextT
END
INSERT INTO Result SELECT @.currK, @.T
END
COMMIT TRAN
CLOSE C
DEALLOCATE C
SELECT * FROM Result
GO
DROP TABLE Result
3. An iterative non-cursor solution is also possible:
SET NOCOUNT ON
GO
--Create a view of the data you want to aggregate
CREATE VIEW Base as
SELECT CustomerID AS KeyCol, rtrim(OrderID) AS Tag
FROM Northwind..Orders
go
--A helpful working table
SELECT A.KeyCol, A.Tag, COUNT(B.Tag) AS TagRank
INTO Working
FROM Base A JOIN Base B
ON A.Tag >= B.Tag
AND A.KeyCol = B.KeyCol
GROUP BY A.KeyCol, A.Tag
CREATE UNIQUE CLUSTERED INDEX Working_KT ON Working(KeyCol,Tag)
--The result table, ultimately, but with no list and two helpful extra
columns
SELECT KeyCol, COUNT(Tag) AS TotalTags, CAST('' AS varchar(8000)) AS TagList
INTO KeyString
FROM Working
GROUP BY KeyCol
CREATE UNIQUE CLUSTERED INDEX KeyString_KT ON KeyString(KeyCol,TotalTags)
--Put the first item in for each key
UPDATE KeyString
SET TagList = K.TagList + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND 0 < K.TotalTags
AND 1 = W.TagRank
DECLARE @.pos int
SET @.pos = 1
--Continue to put items in where there are any left
WHILE @.@.rowcount > 0 BEGIN
SET @.pos = @.pos + 1
UPDATE KeyString
SET TagList = K.TagList + ',' + W.Tag
FROM KeyString K JOIN Working W
ON K.KeyCol = W.KeyCol
AND @.pos <= K.TotalTags
AND @.pos = W.TagRank
END
--What did we get?
SELECT * FROM KeyString
ORDER BY KeyCol
--Clean up
DROP VIEW Base
DROP TABLE Working
DROP TABLE KeyString
SK
imparfait wrote:
>Hi Steve,
>Thank you for ur reply.
>I was surprized by the poorness of aggregation function number ( about a
>dozen ).
>My aim is to return for example the concatenation of some field on the
>grouped by set.
>here's a simplified details example:
>ID | NAME
>1 | foo1
>2 | foo1
>3 | foo2
>4 | foo2
>and I want to return the concatenantion of the IDs having the same NAME
>something like :
>select my_own_aggregation_function(ID,','), NAME from my_table group by NAME
>would return :
>1,2 | foo1
>3,4 | foo2
>the second argument of my_own_aggregation_function is a separator ( in this
>example it's a "," )
>Of course I can solve this another way but creating my own aggregation
>function seems to me the most evident thing to think about as it avoids to
>make an additional query.
>Note : I am working on a huge database ( millions of lines...)
>Thanx again for your answer.
>
>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
>news:eML4JizWEHA.808@.tk2msftngp13.phx.gbl...
>
>>No, though there may be a way in SQL Server 2005. What aggregate do you
>>need? Some aggregates that aren't built in can still be implemented in
>>the current version.
>>Steve Kass
>>Drew University
>>imparfait wrote:
>>
>>Hi all,
>>Is there any way to create my own aggregate functions on SQl server 2000
>>
>?
>
>>I know that PostgreSQL have a CREATE AGGREGATE for that matter. Is there
>>any aquivalent for SQL server ?
>>
>>Thanx in advance for your replies.
>>
>>
>>
>
>|||Thank you very much for ur precise and detailed answer.
"Steve Kass" <skass@.drew.edu> a écrit dans le message de
news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> There is no feature of SQL Server specifically for this requirement,
> though user-defined aggregates may be available in SQL Server 2005.|||impafait
You dont have to know a maximum number of items
Look at below soultion works for you
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"imparfait" <imparfait@.noway.noway> wrote in message
news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
> Thank you very much for ur precise and detailed answer.
>
> "Steve Kass" <skass@.drew.edu> a écrit dans le message de
> news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > There is no feature of SQL Server specifically for this requirement,
> > though user-defined aggregates may be available in SQL Server 2005.
>|||Uri,
While this works in many situations, it's not supported or documented,
and I don't recommend using it in a production environment.
SK
Uri Dimant wrote:
>impafait
>You dont have to know a maximum number of items
>Look at below soultion works for you
>create table w
>(
> id int,
> t varchar(50)
>)
>insert into w values (1,'abc')
>insert into w values (1,'def')
>insert into w values (1,'ghi')
>insert into w values (2,'ABC')
>insert into w values (2,'DEF')
>select * from w
>
>create function dbo.fn_my ( @.id int)
>returns varchar(100)
>as
>begin
>declare @.w varchar(100)
> set @.w=''
> select @.w=@.w+t+',' from w where id=@.id
> return @.w
>end
>select id,
>dbo.fn_my (dd.id)
>from
>(
> select distinct id from w
>)
>as dd
>drop function dbo.fn_my
>"imparfait" <imparfait@.noway.noway> wrote in message
>news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
>
>>Thank you very much for ur precise and detailed answer.
>>
>>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
>>news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
>>
>>There is no feature of SQL Server specifically for this requirement,
>>though user-defined aggregates may be available in SQL Server 2005.
>>
>>
>
>|||Think it's documented in a white paper somewhere - I've lost the reference now.
Should be
declare @.w varchar(100)
select @.w=coalesce(@.w+',','') +t from w where id=@.id
return @.w
"Steve Kass" wrote:
> Uri,
> While this works in many situations, it's not supported or documented,
> and I don't recommend using it in a production environment.
> SK
> Uri Dimant wrote:
> >impafait
> >You dont have to know a maximum number of items
> >Look at below soultion works for you
> >create table w
> >(
> > id int,
> > t varchar(50)
> >)
> >
> >insert into w values (1,'abc')
> >insert into w values (1,'def')
> >insert into w values (1,'ghi')
> >insert into w values (2,'ABC')
> >insert into w values (2,'DEF')
> >select * from w
> >
> >
> >create function dbo.fn_my ( @.id int)
> >returns varchar(100)
> >as
> >begin
> >declare @.w varchar(100)
> > set @.w=''
> > select @.w=@.w+t+',' from w where id=@.id
> > return @.w
> >end
> >
> >select id,
> >dbo.fn_my (dd.id)
> >from
> >(
> > select distinct id from w
> >)
> >as dd
> >
> >drop function dbo.fn_my
> >"imparfait" <imparfait@.noway.noway> wrote in message
> >news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
> >
> >
> >>Thank you very much for ur precise and detailed answer.
> >>
> >>
> >>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
> >>news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
> >>
> >>
> >>There is no feature of SQL Server specifically for this requirement,
> >>though user-defined aggregates may be available in SQL Server 2005.
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>|||Nigel,
The use of variables is documented, but as far as I know, if a select
statement doesn't produce a result set, the only guarantee here is that
@.w will be assigned at least one value, and no guarantee that there will
be one assignment for every row in the table, let alone any guarantee
about the order of assignments. The only mention of this I know of is
http://support.microsoft.com/default.aspx?scid=kb;en-us;287515, which
says "The correct behavior for an aggregate concatenation query is
undefined", and while it also says "In order to achieve the expected
results from an aggregate concatenation query, apply any Transact-SQL
function or expression to the columns in the SELECT list rather than in
the ORDER BY clause," I have my doubts whether that sole off-handed
remark in a relatively confusing KB article is a real indication that
Microsoft intends this technique to be reliable.
SK
Nigel Rivett wrote:
>Think it's documented in a white paper somewhere - I've lost the reference now.
>Should be
>declare @.w varchar(100)
> select @.w=coalesce(@.w+',','') +t from w where id=@.id
> return @.w
>
>"Steve Kass" wrote:
>
>>Uri,
>> While this works in many situations, it's not supported or documented,
>>and I don't recommend using it in a production environment.
>>SK
>>Uri Dimant wrote:
>>
>>impafait
>>You dont have to know a maximum number of items
>>Look at below soultion works for you
>>create table w
>>(
>>id int,
>>t varchar(50)
>>)
>>insert into w values (1,'abc')
>>insert into w values (1,'def')
>>insert into w values (1,'ghi')
>>insert into w values (2,'ABC')
>>insert into w values (2,'DEF')
>>select * from w
>>
>>create function dbo.fn_my ( @.id int)
>>returns varchar(100)
>>as
>>begin
>>declare @.w varchar(100)
>> set @.w=''
>> select @.w=@.w+t+',' from w where id=@.id
>> return @.w
>>end
>>select id,
>>dbo.fn_my (dd.id)
>>from
>>(
>>select distinct id from w
>>)
>>as dd
>>drop function dbo.fn_my
>>"imparfait" <imparfait@.noway.noway> wrote in message
>>news:%23ZsOuM6WEHA.3640@.TK2MSFTNGP11.phx.gbl...
>>
>>
>>Thank you very much for ur precise and detailed answer.
>>
>>"Steve Kass" <skass@.drew.edu> a écrit dans le message de
>>news:eH6Yxx5WEHA.2520@.TK2MSFTNGP12.phx.gbl...
>>
>>
>>There is no feature of SQL Server specifically for this requirement,
>>though user-defined aggregates may be available in SQL Server 2005.
>>
>>
>>
>>
>>
>>
>>