sql-servert-sqlgroup-bystring-aggregation

Substitute for STRING_AGG pre SQL Server 2016


I need to group a table by a set of values together with all matching row numbers/id:s for each set. This operation must be done within the boundaries of SQL Server 2016.

Let's suppose I have the following table (Places):

ID Country City
1 Sweden Stockholm
2 Norway Oslo
3 Iceland Reykjavik
4 Sweden Stockholm

The result that I'm after (No curly-brackets because Stack Overflow thinks it's code, preventing me from posting):

ID Json
1,4 "Country":"Sweden","City":"Stockholm"
2 "Country":"Norway ","City":"Oslo"
3 "Country":"Iceland ","City":"Reykjavik"

In SQL Server 2017 the above result can be achieved with:

SELECT STRING_AGG(ID) ID, (SELECT Country, City FOR JSON PATH) Json
FROM Places GROUP BY Country, City

I managed to get a similar result in SQL Server 2016 with the code below. (But with my actual amount of data and columns, this solution is too slow.)

SELECT DISTINCT Country, City INTO #temp FROM Places
SELECT (SELECT ID From Places WHERE Country = P.Country AND City = P.City FOR JSON PATH) ID, 
(SELECT Country, City FOR JSON Path) Json FROM #temp P

Is there any more performance-effective way of achieving the result that I'm after?

EDIT: As people suggested me to try "FOR XML Path" I tried the code below. This gives the following error "Places.ID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause ":

SELECT stuff((select ',' + cast(ID as varchar(max)) for xml path ('')), 1, 1, '') ID, 
(SELECT Country, City FOR JSON PATH) Json
FROM Places GROUP BY Country, City

Solution

  • Here's a solution you can try with for xml path

    Basically select and group the json columns needed and using an apply, use the for xml path solution to aggregate the correlated ID values; because the outer query needs to refer to the output of the apply it needs to be aggregated also, I chose to use max

    select max(x.Ids), (select country,city for json path) as [Json]
    from t
    outer apply (
        select Stuff((select ',' + Convert(varchar(10),t2.Id)
        from t t2
        where t2.city=t.city and t2.country=t.country
        for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Ids
    )x
    group by country,city
    

    Working Fiddle