Cannot be used in the PIVOT operator because it is

Question

I create an aggregate function for string column in SQL Server 2008.

C# code look like this:

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public struct strconcat : IBinarySerialize
{
    private List<String> values;

    public void Init()
    {
        this.values = new List<String>();
    }

    public void Accumulate(SqlString value = new SqlString())
    {
        this.values.Add(value.Value);
    }

    public void Merge(strconcat value)
    {
        this.values.AddRange(value.values.ToArray());
    }

    public SqlString Terminate()
    {
        return new SqlString(string.Join(", ", this.values.ToArray()));
    }

    public void Read(BinaryReader r)
    {
        int itemCount = r.ReadInt32();
        this.values = new List<String>(itemCount);
        for (int i = 0; i <= itemCount - 1; i++)
        {
            this.values.Add(r.ReadString());
        }
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.values.Count);

        foreach (string s in this.values)
        {
            w.Write(s);
        }
    }
}

And query in SQL:

DECLARE @listCol NVARCHAR(2000)

SELECT  @listCol = STUFF(( SELECT '],[' + A.Name
                        FROM Attribute A,Category C
                        WHERE A.CategoryId = C.Id
                        ORDER BY A.DisplayOrder DESC
                        FOR XML PATH('')), 1, 2, '') + ']'
DECLARE @query NVARCHAR(2000)
SET @query =

N'SELECT * FROM (SELECT P.*,A.Name AttributeName,PA.OriginalValue FROM Product P,Product_Attribute PA, Attribute A WHERE P.Id = PA.ProductId AND A.Id = PA.AttributeId) src
PIVOT 
(
    dbo.strconcat(OriginalValue) FOR AttributeName 
    IN ('+@listCol+')) AS pvt'

EXECUTE (@query)

But SQL Server returns an error:

Msg 406, Level 16, State 1, Line 5
dbo.strconcat cannot be used in the PIVOT operator because it is not invariant to NULLs.

I googled it but don't know how to fix it.

Please help me!

Answer 1

If your aggregate is invariant to nulls, you need to mark it as such in the SqlUserDefinedAggregateAttribute, something like:

[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000,
   IsInvariantToNulls = true)]

The IsInvariantToNulls property describes the requirement as:

Used by the query processor, this property is true if the aggregate is invariant to nulls. That is, the aggregate of S, {NULL} is the same as aggregate of S. For example, aggregate functions such as MIN and MAX satisfy this property, while COUNT(*) does not.

Looking at your aggregate, I think you might need to do some work in your Add method - if the passed in value is null, maybe don't add it to the list?