0

I have a table as below :

DECLARE @t1 TABLE
(
    Country VARCHAR(MAX) NOT NULL,
    AccountName VARCHAR(MAX) NOT NULL,
    DealReference VARCHAR(MAX),
    [Probability 0%] INT,
    [Probability 50%] INT,
    [Probability 100%] INT
)

INSERT INTO @t1 (Country, AccountName, DealReference, [Probability 0%], [Probability 50%], [Probability 100%]) VALUES
     ('Austria', 'Tech Data Vienna', 'AT-379871323', 0, 8000, 0),
     ('Austria', 'Tech Data Vienna', 'AT-379871323', 3000, 0, 0),
     ('Finland', 'Fly Logictics', 'FN-3897214', 6000, 0, 0),
     ('Germany', 'Electronics De Gmbh', 'DE-20948332', 4000, 0, 0 ),
     ('Germany', 'Electronics De Gmbh', 'DE-2174634', 0, 2000, 0 ),
     ('Norway', 'MK distribution Oslo', 'NE-3539232', 9000, 0, 0),
     ('Sweden', 'Bio Pharm Stockholm', 'SE-3897214', 2500, 0, 0),
     ('Sweden', 'Bio Pharm Stockholm', 'SE-3897214', 0, 0, 1000);

select * from @t1 order by 1

I want to transform @t1 into the below result.

Basically I will do group by Country and AccountName and add the Total column at the end. While doing the group by, I want to merge the DealReference values seperate by a comma. In case there is one unique value for multiple rows it will catch only one value.

enter image description here

Below is my solution, everything works apart apart the column DealReference I couldn't figure out how to merge the values into one cell.

I'm using SQL Server Management Studio 18.11.1, based on the official documentation and multiple answered threads here I should use STRING_AGG(DealReference, ', ')

But I'm getting this error

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

This solution for example from a similar issue works for me but the XML PATH is very slow when I run the query on +100k rows.

any suggestions please what I am doing wrong ? Thank you very much.

DevTN
  • 511
  • 2
  • 9
  • 35
  • 3
    *"I'm using Sql Server 2018"* There is no such thing. The latest version of SQL Server is 2019 and the version prior to that was 2017; *both* support `STRING_AGG`. – Thom A May 24 '22 at 15:19
  • 1
    Does this answer your question? [String\_agg for SQL Server before 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017) – Thom A May 24 '22 at 15:19
  • "*This [solution](https://stackoverflow.com/questions/35172956/how-to-concatenate-many-rows-with-same-id-in-sql) for example from a similar issue works for me but the **XML PATH** is very slow when I run the query on +100k rows.*" using `FOR XML PATH` is a slower method, as it requires a correlated subquery, not aggregation; this will likely mean 2 scans (or at best seeks) of your table rather than 1. If `FOR XML PATH` is too slow, then getting onto a (fully) support version of SQL Server is a must so that you can use `STRING_AGG`. – Thom A May 24 '22 at 15:21
  • @Larnu sorry I made a typo, i meant SQL Server Management Studio 18. I corrected it now. – DevTN May 24 '22 at 15:24
  • The version of SSMS doesn't have any relation to the version of SQL Server you are using though @DevTN . SSMS 18 supports SQL Server 2008-2019 (that's 6.5 different versions). What version of SQL Server are you using? – Thom A May 24 '22 at 15:25
  • @Larnu I used the solution you mentioned earlier today and couldn't make it work. I am still getting error. The only way i could make it work was with XML PATH but the query was running for around 8 minutes which is very long for a user wait. – DevTN May 24 '22 at 15:26
  • @Larnu the version is 15.0.18410.0 – DevTN May 24 '22 at 15:27
  • That isn't a (currently released) build number either, @DevTN. The latest released build is SQL Server 2019 CU 16 (build 15.0.4223.1). If you *are* using Version 15 (SQL Server 2019) you would not get the error *"'STRING_AGG' is not a recognized built-in function name."* What is the output of `@@VERSION`? Though all my comments above stand: you *have* to use `FOR XML PATh` or you need to upgrade. – Thom A May 24 '22 at 15:30
  • *"and couldn't make it work"* Then post your attempt using `FOR XML PATH`. – Thom A May 24 '22 at 15:30
  • 1
    If using Sql2019 need to check the database compatability level, if set to 2016 or earlier you'll get the unrecognized function error. – Stu May 24 '22 at 15:42

0 Answers0