1 소스

2 컴파일

3 DB에 설치

4 sample

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Text

<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, 

IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> _
Public Class Accumulator
    Implements IBinarySerialize

    ''' <summary>
    ''' The variable that holds the intermediate result of the concatenation
    ''' </summary>
    Private intermediateResult As SqlDecimal 

    ''' <summary>
    ''' Initialize the internal data structures
    ''' </summary>
    Public Sub Init()
        Me.intermediateResult = 0
    End Sub

    ''' <summary>
    ''' Accumulate the next value, not if the value is null
    ''' </summary>
    ''' <param name="value"></param>
    Public Sub Accumulate(ByVal value As SqlDecimal)
        If value.IsNull Then
            Return
        End If

        Me.intermediateResult += value
    End Sub
    ''' <summary>
    ''' Merge the partially computed aggregate with this aggregate.
    ''' </summary>
    ''' <param name="other"></param>
    Public Sub Merge(ByVal other As Accumulator)
        Me.intermediateResult += other.intermediateResult
    End Sub

    ''' <summary>
    ''' Called at the end of aggregation, to return the results of the aggregation.
    ''' </summary>
    ''' <returns></returns>
    Public Function Terminate() As SqlDecimal 
        Return Me.intermediateResult
    End Function

    Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
        intermediateResult = new SqlDecimal(r.ReadDecimal())
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
        w.Write(Me.intermediateResult.Value)
    End Sub
End Class

vbc.exe /t:library "c:\clr\accumulator.vb"

--drop assembly accumulator 
create assembly accumulator 
from 'c:\clr\accumulator.dll';
go
 
create aggregate acc(@input decimal(38,3)) returns decimal(38,3)
external name accumulator.Accumulator; --대소문자 구문함
go

--drop table #temp3
create table #temp3
(
	gubun int
,	val int
)
insert #temp3 values(1,1);
insert #temp3 values(1,2);
insert #temp3 values(1,3);
insert #temp3 values(2,4);
insert #temp3 values(2,5);
insert #temp3 values(3,6);
insert #temp3 values(4,7);
go

select
    gubun
,   ods.dbo.acc(val)
from #temp3
group by
    gubun
/*
gubun       
----------- ---------------------------------------
1           6.000
2           9.000
3           6.000
4           7.000
*/