sql server 2016 中的 String_agg [英] String_agg in sql server 2016

问题描述

这是我在 sql server 2016 中的代码

Here is my code in sql server 2016

insert into @entdef_queries(entitydefid,squery)
            select A.entitydefid
                ,
                (
                    select String_agg(cols,ioperator)
                    from
                    (
                        Select case when lower(b.metricdatatype) like 'string%' or lower(b.metricdatatype) like '%char%' or lower(b.metricdatatype) ='bit' or lower(b.metricdatatype) like 'date%' then
                                ' lower("'+ b.metricname +'") ' + b.metriccondition +' '''+ b.value1 +''' ' 
                            when lower(b.metricdatatype) not like 'string%' and lower(b.metricdatatype) like '%char%' and lower(b.metricdatatype) !='bit' and lower(b.metricdatatype) not like 'date%' then 
                                case when lower(b.metriccondition)='between' then  ' "'+ b.metricname +'"' + b.metriccondition +' '+ b.value1 +' and ' + b.value2 + ' '
                                    else ' "'+ b.metricname +'"' + b.metriccondition +'  '+ b.value1 + ' ' end  
                            end cols
                        , ( select distinct operators from @entdef_data C where A.entitydefid=C.entitydefid) ioperator
                        from 
                        @entdef_data B
                        where A.entitydefid=b.entitydefid
                    )inp
                )
            from
            @entdef_data A
            group by A.entitydefid;   

当我尝试执行以下代码时..它抛出错误 String_agg 不是内置函数.

When I try to execute the following code..its throwing an error String_agg is not a built in function.

推荐答案

正如 Gordon Linoff 提到的，此功能在 SQL Server 2016 中不可用.

As Gordon Linoff mentioned, this feature is not available in SQL Server 2016.

使用for xml方法.

稍微更快可用于启动 SQL Server 2005 的替代方法:SQLCLR GROUP_CONCAT.用法与本机 STRING_AGG 非常相似.但是因为它是一个自定义的 CLR 聚合，它会带来自己的风险:要启用 CLR、可能的内存泄漏等.

There is a slightly faster alternative that can be used starting SQL Server 2005: SQLCLR GROUP_CONCAT. The usage is pretty similar to a native STRING_AGG. But because it is a custom CLR aggregation, it introduces own risks: CLR to be enabled, possible memory leaks etc..

文档:https://orlando-colamatteo.github.io/ms-sql-server-group-concat-sqlclr/

这篇关于sql server 2016 中的 String_agg的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！