I read here (and elsewhere) that it's possible, in SQL Server 2008, to build a user-defined aggregate which can return a string longer than 8000 characters. This is exactly what I need.
Supposedly, the method is to set maxByteSize to -1 instead of a number btw 1 and 8000; this should allow any size up to 2GB.
For some reason, apparently, you can't deploy straight from Visual Studio 2008 if you use this setting; so you need to manually deploy.
So: I build my project - GroupConcat (which is supposed to simulate MySQL's group_concat aggregator) - which gives me, in the project's bin folder, a file "SqlClassLibrary.dll". Per the instructions on the above-linked page, I build the assembly in SQL Server. The command executes successfully. However, when I try to actually use the groupconcat aggregator:
select department, dbo.groupconcat(projectNumber) from projectleads group by department
...it says it can't be found. This all works fine if I set maxByteSize to 8000 and deploy directly from within VS2008, but I need >8000. Anybody know what I'm doing wrong?
Thanks -dan
NOTE: I do specifically need to have a groupconcat aggregator function rather than using some of the SQL Server tricks I've often seen.
Figured it out... After building the solution in Vis Studio, assuming I've dropped the .dll it creates into c:\temp and called it GroupConcat.dll:
CREATE ASSEMBLY GroupConcat from 'C:\temp\GroupConcat.dll' with permission_set = safe
GO
CREATE AGGREGATE groupconcat(@input nvarchar(max))
RETURNS nvarchar(max)
EXTERNAL NAME GroupConcat
GO
That does it.