Find and Replace values in curly braces with values coming from other column

Question

I have trouble in writing a SQL query.

I have a data table with below columns.

DataTable 1

              id     notes
           ----------------------------------------------------------------
             1      The Organization
             1      develop document disseminate to {{param = "ac-1_prm_1"}}
             2      develop document  to {{param = "ac-1_prm_2"}}
             2       Test

Data table Parameter

 parameterid     value.                 Id
 -------------------------------—————----------------
 ac-1_prm_1      apple doc.              1
 ac-1_prm_2      google doc.             1
 ac-1_prm_3      facebook doc.           2

I need create a final_notes column:

 id  notes                                                       final_notes 
 ---------------------------------------------------------------------------------------------     
  1   The Organization                                  The Organization
  1   develop document disseminate                      develop document disseminate to apple 
     to{<!-- -->{param = "ac-1_prm_1"}}                 doc and google doc
     and {<!-- -->{param = "ac-1_prm_2"}}        
      
 
  2   develop document                                  develop document facebook doc 
      to {<!-- -->{param = "ac-1_prm_3"}}
  2   Test                                              Test

Sql Code does not work.

In my code I have to hard code the value which I dont want.

     DECLARE @DataTable TABLE (
      id int,
       notes varchar(1000)
       );
     INSERT INTO @DataTable VALUES 
    (1, 'Organization'),
    (1, 'develop document disseminate to {{param = "ac-1_prm_1"}} and 
    {{param = "ac-1_prm_2"}} '),
    (2, 'develop document  to {{param = "ac-1_prm_3"}}'),
    (2, 'test');

   DECLARE @DataTableParameter TABLE (
    parameterid varchar(100),
    [value] varchar(100),
    id int
   );
   INSERT INTO @DataTableParameter VALUES
   ('ac-1_prm_1', 'apple doc.', 1),
   ('ac-1_prm_2', 'google doc.', 1),
   ('ac-1_prm_3', 'facebook doc.', 2)

 ;WITH CTE AS (
 SELECT t1.id, t1.notes, t2.parameterid, t2.value 
 FROM @DataTable AS t1
 INNER  JOIN  @DataTableParameter AS t2 ON t1.id = t2.id
)
  SELECT 
 t.id, 
 REPLACE('develop document disseminate to {' + STUFF(
     (
         SELECT ', {' + parameterid + '}'
         FROM CTE 
         WHERE id = t.id 
         ORDER BY parameterid
         FOR XML PATH(''), TYPE
     ).value('.', 'NVARCHAR(MAX)'),
     1,
     1,
     ''
    ) + '}', ',', ' and ') AS notes,
   REPLACE(REPLACE('develop document disseminate to ' + STUFF(
     (
         SELECT ', ' + value
         FROM CTE 
         WHERE id = t.id 
         ORDER BY value
         FOR XML PATH(''), TYPE
     ).value('.', 'NVARCHAR(MAX)'),
     1,
     1,
     ''
 ), '.', ''), ',', ' and ') AS final_notes
   FROM CTE AS t
  GROUP BY t.id;

Answer 1

A recursive CTE is probably going to be rather unwieldy here, as you cannot use TOP and you must also filter only the final results.

Instead use a table variable or temp table, and update it in a loop.

DECLARE @results TABLE (id int, notes varchar(1000));

INSERT @results (id, notes)
SELECT id, notes
FROM @DataTable dt;

DECLARE @dtp varchar(100), @dtv varchar(100);

WHILE 1=1
BEGIN
    SELECT TOP (1)
        @dtp = dtp.parameterid,
        @dtv = dtp.value
    FROM @DataTableParameter dtp
    WHERE parameterid > @dtp OR @dtp IS NULL
    ORDER BY parameterid;

    IF @@ROWCOUNT = 0
        BREAK;
    
    UPDATE @results
    SET
        notes = REPLACE(notes, '{{param = "' + @dtp + '"}}', @dtv)
    FROM @results r
    WHERE notes LIKE '%' + @dtp + '%';
END;

SELECT *
FROM @results;

db<>fiddle