How to replace multiple string in a single select statement?

Question

I'm trying to replace strings in a table but couldnt get what I need. Below is the sample code for replacing strigns which I tried.

Problem: I need to replace the string 'CV7 + CV8' with exact matching values.

For eg: if I have values like CV7 1998,CV8 1998 which needs to replaced as CV7 1998 + CV8 1998 without any duplicates. so my desired ouptut should be

CV7 1998 + CV8 1998
CV7 1999 + CV8 1999
.
.

How can I achieve this in a single selece statement ?

 Begin
Declare @variable as varchar(50)
set @variable = 'CV7 + CV8'

CREATE TABLE #CVtableforallyears
(
Outcomedestination Varchar(160),
TimeDimensionDatefromCV date
)
 Insert into #CVtableforallyears values 
('CV7 1998','1998-01-01'),
('CV7 1999','1999-01-01'),
('CV7 2000','2000-01-01'), 
('CV7 2001','2001-01-01'),   
('CV7 2002','2002-01-01'),
('CV8 1998','1998-01-01'),
('CV8 1999','1999-01-01'),
('CV8 2000','2000-01-01'), 
('CV8 2001','2001-01-01'), 
('CV8 2002','2002-01-01')

Select Replace(REPLACE(@variable,'CV7',LTRIM (RTRIM (Outcomedestination))),'CV8',Outcomedestination) from #CVtableforallyears
Drop table #CVtableforallyears
END

Answer 1

This ought to do you:

SELECT DISTINCT
  substring(
   (SELECT 
      DISTINCT ' + ' + t2.Outcomedestination
    FROM #CVtableforallyears t2 
    WHERE t2.TimeDimensionDatefromCV = t1.TimeDimensionDatefromCV
    FOR XML Path ('')  
    )
  ,4,1000) --just to omit the first ' + '
FROM #CVtableforallyears t1; 
END

It works for solo CVs and more than 2.

Edited to pare down the unnecessary .The example below demonstrates using FOR XML PATH and then how to use that in your full query.

Basically we concatenate the entire query into a string but use '' to split where naturally <row> would occur; we concatenate each result with ' + '. Then you make your subquery only select matches to the parent query. Using DISTINCT to avoid duplication. Unfortunately there is no LISTAGG in TSQL so we use this method.

Here is an example