sql server 2016 中的 String

本文介绍了sql server 2016 中的 String_agg的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我在 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/