1

I've seen people combining both FOR XML and STUFF statements to achieve a STRAGG-like in Sql Server.

I have a problem with this approach in that, given I have a couple of fields I want to aggregate into strings to the outermost query, I am repeating the same select statement over and over again, for each new aggregation.

As far as I understand, however, this FOR XML + STUFF solution has to be applied in the innermost level - meaning I can't add an inline view with the select statement I will be using in the aggregations and apply Distinct on it, because I will already have joined the results with each of the distinct values.

In a short, adding to the example taken from the site cited above, here's what I want to do:

http://www.sqlfiddle.com/#!3/84199/2/0

Is there any better solution when you want to do that for a number of aggregations, so to avoid such redundant performance penalties ?

Community
  • 1
  • 1
Veverke
  • 9,208
  • 4
  • 51
  • 95
  • What do you mean by better (shorter)? Using Oracle `LISTAGG` or MySQL `GROUP_CONCAT` you have to specify them for each concated column. SqlServer has this workaround with `FOR XML and STUFF` and it needs to be repeated. – Lukasz Szozda Sep 06 '15 at 09:01
  • That's what I wanted to avoid - having to repeat the same Selection over and over again, in Sql Server – Veverke Sep 06 '15 at 09:04
  • It isn't shorter but you can use [this syntax](http://www.sqlfiddle.com/#!3/84199/15/0) – Lukasz Szozda Sep 06 '15 at 09:11
  • @lad2025: where is the gain ? I will do the same cross apply twice – Veverke Sep 06 '15 at 09:13
  • 1
    There isn't any gain in length, but your select is clearer, I doubt you find more concise approach. Read [Concatenating Row Values in Transact-SQL](https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/) – Lukasz Szozda Sep 06 '15 at 09:20
  • That's why I posted the question... thanks for helping, anyway. By the way, when I say "gain" I mean in performance. – Veverke Sep 06 '15 at 09:21
  • 1
    If you found shorter solution without Dynamic-SQL please let me know. – Lukasz Szozda Sep 06 '15 at 09:22

0 Answers0