SQL Server Creating user defined aggregate function returning nvarchar

rur96b6h 于 12个月前发布在其他

关注(0)|答案(1)|浏览(109)

I need to create an aggregation function to use for summarizing data.

The function basically should return "#" if more than 1 distinct value in column and return the value if only one distinct value in column.

What I tried so far is;

CREATE FUNCTION dbo.my_agg(@mycol NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX);

    SELECT @result = CASE
                        WHEN COUNT(DISTINCT @mycol) = 1 THEN MAX(@mycol)
                        ELSE '#'
                    END

    RETURN @result;
END;

Then I realized user defined functions cannot be used in aggregation, I need to run this function in my grouped data via group by.

I think the definition should start with CREATE AGGREGATE but couldn't find how to implement.

Thanks in advance.

sql-server

来源：https://stackoverflow.com/questions/77708367/creating-user-defined-aggregate-function-returning-nvarchar

1条答案

按热度按时间

p8h8hvxi1#

Apart from the comments above, it will not work also because you can't pass the column name this way. If you pass @mycol, let's say, 'column1' - it will not calculate COUNT(DISTINCT column1). Instead it will calculate COUNT(DISTINCT 'column1') and obviously will always give you 1.

It could be worked around with dynamic SQL, but it's not possible in the UDF in this scenario.

What you could possibly try is to prepare all aggregations in advance when building a temp table with your query, and then use one of them in the main query:

SELECT
    column1,
    column2,
    ...
    columnN,
    CASE WHEN COUNT(DISTINCT column1) = 1 THEN MAX(column1) ELSE '#' END AS Aggregation1,
    CASE WHEN COUNT(DISTINCT column2) = 1 THEN MAX(column2) ELSE '#' END AS Aggregation2,
    ...
    CASE WHEN COUNT(DISTINCT columnN) = 1 THEN MAX(columnN) ELSE '#' END AS AggregationN
INTO #Temp
FROM
(
    SELECT -- your initial query
        Something1 AS Column1,
        Something2 AS Column2,
        ...
        SomethingN AS ColumnN
    FROM ...
) q;

DECLARE @myCol VARCHAR(100) = 'column1';

SELECT
    column1,
    column2,
    ...,
    columnN,
    CASE @myCol
        WHEN 'column1' THEN Aggregation1
        WHEN 'column2' THEN Aggregation2
        ...
        WHEN 'columnN' THEN AggregationN
    END AS Aggregation
FROM #Temp;

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Creating user defined aggregate function returning nvarchar

1条答案

相关问题

热门标签

最新问答