Is there a way that I can replace a substring in SQL server while remaining 'aware' of what I'm replacing so that I can then re-insert that content?
I am building a string of column names to pivot on, and these column names need to be aggregated on in the query.
For example, I have this list of columns as an nvarchar(max) string:
[A], [B], [C]
And I would like to transform this string to look like this:
sum([A]) [A], sum([B]) [B], sum([C]) [C],
I have just found the
stuff function but I don't know how to achieve what I want yet.
But I get the feeling that I can use it to insert substrings instead of using replacing substrings.
Looking at the stuff function:
STUFF( source_string, start, length, add_string )
How do you insert into multiple locations at once?
My current solution is this:
declare @cols nvarchar(max); declare @colsAggregate nvarchar(max); declare @sql nvarchar(max); select @cols = stuff(( select distinct ',' + quotename(sCode) from EntityList EL where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, ''); select @colsAggregate = stuff(( select distinct ', max(' + quotename(sCode) + ') ' + quotename(sCode) from EntityList EL where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');