0

I have a code snippet that works fine in MS SQL 2019, but it doesn't work in MS SQL 2016. I know the problem is caused by the STRING_AGG function. When I searched the web, I saw solutions for a single column. But this seems a little more complicated. So, How do I convert a query containing more than one STRING_AGG to FOR XML PATH?

Here is the code snippet:

SELECT DISTINCT
    STRING_AGG(rvsm.Plate, ',') WITHIN GROUP (ORDER BY rvsm.Plate),
    MIN(rvsm.Date),
    MIN(rvsm.StopId),
    rvsm.RouteVariantId,
    MIN(rvsm.ValidatorNo),
    MIN(rvsm.Id),
    COUNT(*),
    STRING_AGG(rvsm.ValidatorNo, ',')
FROM
    VehicleTrackingLastLoc as rvsm
    INNER JOIN RouteVariantStops rvs ON rvsm.RouteVariantId = rvs.RouteVariantId AND rvsm.StopId = rvs.StopsId
    INNER JOIN RouteVariant rt ON rvsm.RouteVariantId = rt.Id
    INNER JOIN Stops st ON rvsm.StopId = st.Id
WHERE
    Contact = 'ON' AND Date > dateadd(second, -30, GetDate())
GROUP BY
    rvs.RowNo,
    rvsm.RouteVariantId
HAVING
    COUNT(*) > 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
Batuhan Karaman
  • 3
  • 1
  • 2
    Using 2 `FOR XML PATH` subqueries. Also, why do you have `DISTINCT` when you have a `GROUP BY`? Either the `DISTINCT` is redundant, or your `GROUP BY` is wrong. – Thom A Jan 04 '21 at 08:59
  • Could you post the sample data, and expected output? It will be better for everyone to help. – Tomato32 Jan 04 '21 at 09:02
  • Does this answer your question? [string_agg for sql server pre 2017](https://stackoverflow.com/q/49361088/2029983) If not, why not? What were your attempts to implement the shown logic, and why didn't it work? – Thom A Jan 04 '21 at 09:15

0 Answers0