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

Improve this question
3
  • 1
    STRING_AGG was introduced in SQL Server 2017. Commented Aug 13, 2019 at 8: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. Commented Aug 13, 2019 at 8: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 Commented Sep 11, 2020 at 10:38

1 Answer 1

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Is there an equivalent function in earlier versions or how would you do it?
you can achieve it by using FOR XML PATH() . let me update the answer with an example

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.