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;
When I try to execute the following code..its throwing an error String_agg
is not a built in function.
As Gordon Linoff mentioned, this feature is not available in SQL Server 2016.
The for xml
approach is to be used.
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..
Documentation: https://orlando-colamatteo.github.io/ms-sql-server-group-concat-sqlclr/