0

This is my data:

enter image description here

Im trying the following code:

SELECT *,
        STRING_AGG(SUBTIPO_PRODUCTO, ' | ')
        WITHIN GROUP (ORDER BY ORDEN_PRODUCTOS) PRODUCTOS
FROM #ORDEN_PRODUCTOS

and I'm getting the following error:

'STRING_AGG' is not a recognized built-in function name.

Is string_agg locked for SMSS 17? Is the code wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ivancito
  • 159
  • 1
  • 11
  • 2
    Run `SELECT @@VERSION` in SSMS and tell us what version it says you are using. – dfundako Sep 30 '19 at 14:03
  • 1
    duplicate of https://stackoverflow.com/questions/40923080/string-agg-is-not-a-recognized-built-in-function-name – Doug Coats Sep 30 '19 at 14:04
  • Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) – Ivancito Sep 30 '19 at 14:21
  • 1
    `STRING_AGG` is only available in SQL Server **2017** or newer - not in 2016..... and it only depends on the **server** (engine) version - not the SSMS version you're using . – marc_s Sep 30 '19 at 16:16

1 Answers1

1

The available functions in SQL Server are determined by the compatibility setting of the database, which is a representation of the version of SQL Server that the database is currently running on. If you are using SQL Server 2014 as a server, SSMS 2017 doesn't grant the STRING_AGG function. You need to be working in SQL Server 2017 or higher for STRING_AGG to work.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sean Brookins
  • 574
  • 4
  • 12
  • This isn't 100% correct I believe. If you have a 2017 install/instance, make a DB with 2012 compatibility level, you can still run things like ISJSON which is only available in 2016 and later. – dfundako Sep 30 '19 at 14:07
  • Hm, I'll have to double check that - I know that when I was using my 2017 instance with a 2012 compatibility, STRING_AGG failed to execute (I had to set compatibility lower after I had written some code, for a class) -- I suppose I can't speak to every function. – Sean Brookins Sep 30 '19 at 14:09
  • I believe this is not accurate since my SQL Server is 2016: Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) – Ivancito Sep 30 '19 at 14:22
  • Hi Ivancito, as I stated in my original answer and was commented on your initial post, `STRING_AGG` requires SQL Server 2017 and you do not have it. – Sean Brookins Sep 30 '19 at 16:43