String_agg in sql server 2016

Question

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.

string_agg() is available starting in 2017, not 2016. Either upgrade SQL Server or use the for xml approach. — Gordon Linoff
– Gordon Linoff, Commented Feb 6, 2019 at 12:02

Alexander Volok · Accepted Answer · 2019-02-06 14:40:30Z

5

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/

edited Feb 6, 2019 at 14:40

answered Feb 6, 2019 at 12:28

Alexander Volok

5,9803 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

String_agg in sql server 2016

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related