不能在PIVOT运算符中使用，因为它不是NULL不变的 [英] Cannot be used in the PIVOT operator because it is not invariant to NULLs

问题描述

我在SQL Server 2008中为字符串列创建一个聚合函数。

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

C＃代码如下：

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);
        }
    }
}

并在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)

但是SQL Server返回错误：

But SQL Server returns an error:

消息406，级别16，状态1，第5行

dbo.strconcat不能在PIVOT运算符，因为它不是NULL不变。

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

我用Google搜索了它，但不知道如何解决。

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

请帮助我！

推荐答案

如果您的汇总是不变为null，则需要在 SqlUserDefinedAggregateAttribute ，类似于：

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)]

IsInvariantToNulls 属性将该要求描述为：

The IsInvariantToNulls property describes the requirement as:

由查询处理器使用，如果聚合不变为null，则此属性为true。也就是说，S的集合{NULL}与S的集合相同。例如，诸如MIN和MAX的集合函数满足此属性，而COUNT（*）不满足此属性。

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.

看看您的汇总，我认为您可能需要在 Add 方法中做一些工作-如果传入值是否为空，也许不将其添加到列表中？

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?

这篇关于不能在PIVOT运算符中使用，因为它不是NULL不变的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！