SQL Server Puzzle - Replicate comma and replace string before comma?

I'm assuming you performing some sort of macro substition.

A couple of note:

1. The Trailing Comma seems to be inconsistent, so I left it out
2. You have Price in the final row, while I have Expired

Example

Select A.ID
      ,A.Lid
      ,B.MyString
      ,A.Did
      ,A.Secid
 From  Test1 A
 Cross Apply (
                Select MyString = Stuff((Select ',' +RetVal 
                  From (
                           Select RetSeq
                                 ,RetVal = max(IsNull(B2.Value,B1.RetVal))
                            From (
                                    Select Top (Select max(CommaPosition) From Test2 Where RowID=A.ID )
                                           RetSeq=Row_Number() Over (Order By (Select null))
                                          ,RetVal='' 
                                     From master..spt_values 
                                    Union All
                                    Select RetSeq = Row_Number() over (Order By (Select null))
                                          ,RetVal = LTrim(RTrim(N.i.value('(./text())[1]', 'varchar(max)')))
                                    From  (Select x = Cast('<x>' + replace((Select replace(A.MyString,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X 
                                    Cross Apply x.nodes('x') AS N(i)
                                 ) B1
                            Left Join Test2 B2 on B2.RowID=A.ID and B2.CommaPosition=B1.RetSeq
                            Group By B1.RetSeq
                        ) B3
                  Order by RetSeq
                  For XML Path ('')),1,1,'') 
             ) B

Returns

The correct answer is don't store multiple values in a single column. This violates first normal form!!!

It's best if you decompose your concatenated strings into a parent and a child table. Your example Table2 which appears to be a set of modification commands already has the required structure--you just need to put the full set of values into a permanent table just like it.

Once you have that, processing your change data is super easy: join on key values and update! Boom, done. Your database should be normalized and should not store data that is prone to modification in a format that requires unpacking it, modifying it, and packing it again.

If you need some way to concatenates values together to produce output like MyString in Table1, make a view for that, or do it in front-end code. There's a wonderful SQL-based solution with FOR XML PATH that can do this easily. Beware of random XML PATH solutions that you might run across as most of the ones out there can't accommodate strings that contain xml-like elements such as > and <. Use the one from this example below (complete with the .value() expression) which can accommodate such characters.

This is a generic example that shows how to combine a parent and child table into single rows, one for each parent, with all the child values concatenated together. You can put an ORDER BY clause into the query using FOR XML to get a particular order.

CREATE TABLE dbo.Parent (
    ParentId int identity(1,1) NOT NULL CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED,
    Name varchar(100) NOT NULL CONSTRAINT UQ_Parent_Name UNIQUE
);

CREATE TABLE dbo.ParentChild (
   ParentId int NOT NULL,
   ChildName varchar(100)
);

INSERT dbo.Parent (Name) VALUES
   ('Parent 1'), ('Parent X'), ('Parent B');

INSERT dbo.ParentChild (ParentId, ChildName) VALUES
   (1, 'ABC'), (1, 'WHOAMAN'), (1, 'QRT'),
   (2, 'XYZ'), (3, 'LMN'), (3, 'MAN'), (3, 'WHOADOG');

SELECT
   p.*,
   Children = Substring((
      SELECT ', ' + ChildName
      FROM ParentChild pc
      WHERE p.ParentId = pc.ParentId
      FOR XML PATH(''), TYPE
   ).value('.[1]', 'varchar(max)'), 3, 8000)
FROM
   dbo.Parent p
WHERE -- an example of filtering by a child value without substrings on the combined string
   EXISTS (
      SELECT *
      FROM dbo.ParentChild pc
      WHERE
         p.ParentId = pc.ParentId
         AND pc.ChildName = 'WHOADOG'
   );