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, '');