0

I have a sql stored preocedure where I am trying to use the string_agg function. When I try to create the stored proc I get the error

Msg 195, Level 15, State 10, Procedure MamographyDaily, Line 18 [Batch Start Line 7] 'STRING_AGG' is not a recognized built-in function name.

I am running SQLserver 2016 with the SQL 2017 management studio

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Marc L
  • 837
  • 9
  • 19
  • 40
  • 1
    `STRING_AGG` was introduced in SQL Server 2017. – Zhorov Aug 13 '19 at 08:37
  • Management studio version is irrelevant - what's relevant is the database compatibility level - and string_agg requires at least 140 - while SQL Server 2016's highest compatibility level is 130. – Zohar Peled Aug 13 '19 at 08:41
  • What is extremely annoying is that if you put SQL Server 2017 into compatibility mode 130 then it lets you use STRING_AGG, which then fails when you put it on a real 2016 server – Whelkaholism Sep 11 '20 at 10:38

1 Answers1

1

STRING_AGG will be supported only If You are using Sql Server version 2017 and higher versions.

Refer: STRING_AGG

Note: If you are using the below versions you can achieve it by using FOR XML PATH()

Example:

Sample Data

 Field A | Field B
    1       |  A
    1       |  B
    2       |  A

Required OutPut

1 | AB
2 | A

Query:

select distinct t1.FieldA,
  STUFF((SELECT distinct '' + t2.FieldB
         from yourtable t2
         where t1.FieldA = t2.FieldA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') data
from yourtable t1;
Mahesh.K
  • 901
  • 6
  • 15