2

I want to convert the following MYSQL query to MS SQL Server. But, I am sure that GROUP_CONCAT doesn't exist in MS SQL Server prior to 2017. There is a function in SQL SERVER 2017. Can anyone help me?

SELECT region, GROUP_CONCAT(route_name) AS route_name 
FROM route_details 
LEFT JOIN ride_details ON route_id = r_id 
WHERE region != '' AND service_date = '2019-09-02' 
GROUP BY region
GMB
  • 216,147
  • 25
  • 84
  • 135
Rubin Anbin
  • 188
  • 2
  • 13

1 Answers1

6

You want string_agg(). The syntax is a bit different (typically, the separator is mandatory, while it defaults to , in MySQL, and SQLServer wants within group for the order by ):

SELECT region, STRING_AGG(route_name, ',') AS route_name 
FROM route_details 
LEFT JOIN ride_details ON route_id = r_id 
WHERE region != '' AND service_date = '2019-09-02' 
GROUP BY region
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 4
    When I execute the above query, it shows the following error STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation. – Rubin Anbin Nov 13 '19 at 14:15
  • 6
    Simply wrap route_name in a convert function. convert(varchar(max), route_name) – Sean Lange Nov 13 '19 at 14:21
  • What do you mean by "the order by clause is spelled differently"? – Sean Lange Nov 13 '19 at 14:21
  • 1
    Perhaps GMB means the syntax is different, rather than the spelling, @SeanLange ? ‍♂️ – Thom A Nov 13 '19 at 14:25
  • 1
    @Larnu hence my confusion....there is no order by in the OP or this code. And AFAIK the syntax for ORDER BY is the same in both sql server and mysql. Regardless of that, this the solution. – Sean Lange Nov 13 '19 at 14:27
  • @SeanLange: AFAIK in SQL Server you need `str_agg(...) within group(order by ...)`, while MySQL says: `group_concat(... order by ...)`. But indeed that wasn't that clear in my answer. – GMB Nov 13 '19 at 14:33
  • 1
    Ahh I understand now what you were saying. Thanks for clarifying, I was really scratching my head. – Sean Lange Nov 13 '19 at 14:40