List() Function

Sybase has implemented a useful aggregate function called list() which takes
a column and returns all values in a comma delimited string. In Oracle10g,
there is a Java solution to duplicate this aggregation function. Now I have
to do the same in SQLServer2005. I found the UDA (User Defined Aggregate)
support in MSSQL2005 and some sample codes in C#. Has anyone done this
before? If someone can upload a precompiled module, it will save me some
effort. TIA.
I am downloading .Net Framework 2.0 SDK right now and hopefully it contains
a C# compiler. Otherwise, I have to buy a copy of Visual Studio .Net just
for this function.Why do you think you need to use a UDA aggregate for this? Seems like
overkill, since several other methods already exist and are widely
available. You may be able to write something that performs a few
milliseconds faster, but it will come at considerable development cost, and
there is plenty of room for new problems.
"mason" <masonliu@.msn.com> wrote in message
news:uqeTYn$NGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Sybase has implemented a useful aggregate function called list() which
> takes a column and returns all values in a comma delimited string. In
> Oracle10g, there is a Java solution to duplicate this aggregation
> function. Now I have to do the same in SQLServer2005. I found the UDA
> (User Defined Aggregate) support in MSSQL2005 and some sample codes in C#.
> Has anyone done this before? If someone can upload a precompiled module,
> it will save me some effort. TIA.
> I am downloading .Net Framework 2.0 SDK right now and hopefully it
> contains a C# compiler. Otherwise, I have to buy a copy of Visual Studio
> .Net just for this function.|||I know. This is a migration project. If I can duplicate as much as possible
at the backend, I'll save tremendous efforts at the frontend. Besides, there
is only one aggregate function I like to create and I found some sample
source codes, it shouldn't be too bad. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23hYedq$NGHA.740@.TK2MSFTNGP12.phx.gbl...
> Why do you think you need to use a UDA aggregate for this? Seems like
> overkill, since several other methods already exist and are widely
> available. You may be able to write something that performs a few
> milliseconds faster, but it will come at considerable development cost,
> and there is plenty of room for new problems.
>
>
> "mason" <masonliu@.msn.com> wrote in message
> news:uqeTYn$NGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Check out 'For Xml Path' in:
http://www.aspfaq.com/show.asp?id=2529
Her is more from Tony Rogerson SQL Server MVP:

>
In SQL Server 2005 we can very simply use some of the new XML features to
get what we want...
select distinct type,
( select name + ', ' as [text()]
from sys.objects s
where s.type = so.type
order by name
for xml path( '' )
) as concatenated_name
from sys.objects so
order by type
This is documented in books online, look up 'Using PATH mode'
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a685a9ad-3d28-4596-aa72-119202df3976.htm)[
color=darkred]
>[/color]
www.rac4sql.net|||Thanks. I read about these solutions on the web. The issue is the list() has
been a very popular function to be called in embedded SQL statements by our
apps and in some stored procedures. If I do not implement this, we have to
hunt down those codes and perform IF ELSE logic, very tedious work. If one
day, a customer insists to use DB2, we have to go thru this process again.
Therefore, finding backend portable solution is usually the most cost
efficient for us.
"05ponyGT" <noname@.overwood.com> wrote in message
news:ekltQ0$NGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Check out 'For Xml Path' in:
> http://www.aspfaq.com/show.asp?id=2529
> Her is more from Tony Rogerson SQL Server MVP:
>
> In SQL Server 2005 we can very simply use some of the new XML features to
> get what we want...
> select distinct type,
> ( select name + ', ' as [text()]
> from sys.objects s
> where s.type = so.type
> order by name
> for xml path( '' )
> ) as concatenated_name
> from sys.objects so
> order by type
> This is documented in books online, look up 'Using PATH mode'
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a685a9ad-3d28-4596-aa72-1
19202df3976.htm)
> www.rac4sql.net
>|||Oh my, a helpful post. :)

> 05ponyGT
Nice ride.|||> Oh my, a helpful post. :)

> Nice ride.
Vert.
3.73 tush,bassani axle backs and x-pipe,jba headers,
aem cai,flashed.
BAD A$& Car
:P|||05ponyGT <nospam@.nospam> wrote:
>
> Vert.
> 3.73 tush,bassani axle backs and x-pipe,jba headers,
> aem cai,flashed.
> BAD A$& Car
> :P
I'm impressed! :)|||I have implemented list() UDA function in MSSQL2005 successfully. The source
codes are adapted from Anthony Trudeau's blog page. Here are the steps if a
nyone's interested.
1. Download .Net Framework 2.0 SDK and/or Visual C# 2005 Express. Both are f
ree. The purpose is to get the C# compiler (csc.exe) and the latter gives yo
u the IDE to config the module version info if you care. Create a class libr
ary project and compile the C# source codes from Anthony Trudeau's example (
make appropriate modifications as I did if necessary). You will get a DLL. T
o skip this step, you can use the DLL I attached to this msg.
2. Run the following SQL to create the list() aggregate function:
CREATE ASSEMBLY UDAFunctions FROM 'C:\TEMP\ListClassLibrary.dll'
go
CREATE AGGREGATE list(@.value NVARCHAR(4000)) RETURNS NVARCHAR(4000)
EXTERNAL NAME [UDAFunctions].[SqlAggregateFunctions.List]
go
Now, you can issue SQLs like the following to get a comma delimited value li
st such as 2001,2003,2006 and CA,TX,NY,IL,FL.
SELECT dbo.list(mycol) from mytable WHERE ...
The only limitation is, as in Oracle10g, that ORDER BY clause is not support
ed, which you can do in Sybase.
"mason" <masonliu@.msn.com> wrote in message news:uqeTYn$NGHA.1028@.TK2MSFTNGP11.phx.gbl...[c
olor=darkred]
> Sybase has implemented a useful aggregate function called list() which tak
es
> a column and returns all values in a comma delimited string. In Oracle10g,
> there is a Java solution to duplicate this aggregation function. Now I hav
e
> to do the same in SQLServer2005. I found the UDA (User Defined Aggregate)
> support in MSSQL2005 and some sample codes in C#. Has anyone done this
> before? If someone can upload a precompiled module, it will save me some
> effort. TIA.
>
> I am downloading .Net Framework 2.0 SDK right now and hopefully it contain
s
> a C# compiler. Otherwise, I have to buy a copy of Visual Studio .Net just
> for this function.
>[/color]|||>> If I do not implement this, we have to hunt down those codes and perform
IF ELSE logic, very tedious work. If one day, a customer insists to use DB2
, we have to go thru this process again. Therefore, finding backend portabl
e solution is usually the m
ost cost efficient for us. <<
Think of this as how God punishes programmers who do not write portable
code in the firsts place. Your backend solution is probably going to
involve different proprietary code in the target product. The reason I
say that is that LIST() is a way to screw up 1NF, so it is not going to
be popular with RDBMS people and it is not part of the Standards. It
was proposed at one time and was voted down.
What you are setting yourself up for is having to maintain (n) code
bases instead of one. Bite the bullet and do it right. Oh, and beat
your developers who wrote you into this corner.
Based on a few decades of patching bad SQL, I am not sure that you will
need IF-THEN-ELSE code in your SQL, if you do it right.