This project has moved. For the latest updates, please go here.

SQL 2005 version

Aug 23, 2012 at 2:15 PM

SQL 2005 does not support multiple paramaters on the accumulate function

In the StringBuilder version (e.g. http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/ ) i had to change

the following to get it to work for sql 2005

Concatenate.cs:

line 29 MaxByteSize = 8000 /// -1 for SQL Server 2008 R2

 line 68 public void Accumulate(SqlString Value  
        //, SqlString Delimiter
        )
    {
        //if (!Delimiter.IsNull

        //    & Delimiter.Value.Length > 0)
        //{

        //    _delimiter = Delimiter.Value; /// save for Merge

            if (_accumulator.Length > 0) _accumulator.Append(
                ',');
                // Delimiter.Value);



        //}

        _accumulator.Append(Value.Value);

        if (Value.IsNull == false) this.IsNull = false;

    }

And in the aggregate creation script

CREATE AGGREGATE dbo.concat (    @Value NVARCHAR(MAX)
--, @Delimiter NVARCHAR(4000) SQL2008
) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.concat;

I will look into changing your source code for usage on SQL 2005 (assembled by VS2008)

Afterwards it would be nice to have a 2005 version of GROUP_CONCAT ready for download.

Coordinator
Aug 26, 2012 at 3:48 PM
Edited Oct 17, 2015 at 7:57 AM
The motivation for creating this set of functions was two-fold:
  1. recreate functionality provided by MySQL GROUP_CONCAT to make it easier to migrate code and leverage existing skills on SQL Server
  2. provide a more expressive syntax than the T-SQL XML methods offer for concatenating grouped strings while not sacrificing performance
I found that I could not achieve either of these goals to my satisfaction on SQL Server 2005 due to these two technical limitations:
  1. lack of support for multiple input parameters
  2. limitation on the max size of a serialized object (8000)
Both restrictions were lifted in the release of SQL 2008. For SQL Server 2005 I recommend using the T-SQL XML methods. I realize there are a lot of SQL Server 2005 installations still in service but keep in mind that it is approaching 7 years since its release and there have been 3 formal releases of SQL Server since that time: 2008, 2008 R2 and recently 2012.