这是我在 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..
文档:orlando-colamatteo.github.io/ms-sql-server-group-concat-sqlclr/
更多推荐
sql server 2016 中的 String
发布评论