0

Is there any other solution to make tables concatenation without FOR XML PATH?

I have this code which I want to somehow improve, because with this code I got errors in other views.

I started to think about using concat or something similar. String_agg not an option here.

[table] = SUBSTRING((SELECT ',' + [PatGroup].[PG_K] AS [text()]
                     FROM D1_EPSDPATGROUPS [Episode]
                     INNER JOIN C1S_PATIENTGROUP [PatGroup]
                           ON [Episode].[PG_ID] = [PatGroup].[PG_ID]
                     WHERE [Episode].[EPSD_ID] = D1_EPISODE.EPSD_ID
                     FOR XML PATH ('')), 2, 1000)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    On a supported version of SQL Server, yes, use `STRING_AGG`. The above method (though using `STUFF` instead of `SUBSTRING` is a far better way to get rid of the leading comma) is the "old" 2016 and prior method. – Thom A Oct 05 '21 at 13:00
  • Without being more specific about how you "somehow" want to improve it it's going to be hard to tell. What "errors" did you get? For that matter, what does your input look like and what's your desired output? – Jeroen Mostert Oct 05 '21 at 13:01
  • my sql serv version does not support string_agg. unfortunately. –  Oct 05 '21 at 13:02
  • 3
    Then you'll need to continue using the "old" `FOR XML PATH` method, @junior , or if you really need `STRING_AGG`, upgrade the version you are using. – Thom A Oct 05 '21 at 13:06
  • "Without being more specific about how you "somehow" want to improve it it's going to be hard to tell. What "errors" did you get? For that matter, what does your input look like and what's your desired output? " in my view, when i put this code, everything works, i got out the data, but this piece also affects another view, because in another view, data is no longer selected because of this code. It is clear that concatenating tables needs to be redesigned differently, so I wonder what other ways this could be done. –  Oct 05 '21 at 13:08
  • 1
    If you are having problems with XML escaping, it's because you haven't got all the syntax. You need `FOR XML PATH (''), TYPE).value('text()[1]','nvarchar(max)')` and you can also use `STUFF` to remove the first character – Charlieface Oct 05 '21 at 13:13
  • But the above expression, to aggregate your string(s), would be in the `SELECT` as a subquery; it won't stop data being returned as it's not part of the `FROM`/`JOIN` or `WHERE`. – Thom A Oct 05 '21 at 13:13
  • 1
    Without `STRING_AGG` there are different ways, but all of them suck in one way or another: `FOR XML PATH` you already know, then there's recursive CTEs, CLR functions and even loops. See [here](https://stackoverflow.com/q/13639262/4137916) for the gory details. If you are having a problem with `FOR XML PATH`, it likely won't be "fixed" by using one of the other methods, as they're substantially more complicated. – Jeroen Mostert Oct 05 '21 at 13:15

0 Answers0