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
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