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;

CodePudding user response：

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

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
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);
-- DDL and sample data population, end

DECLARE @parameterid varchar(100), @value VARCHAR(100);
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @DataTableParameter);

WHILE @RowCount > 0 BEGIN
   SELECT @parameterid = parameterid, @value = value
   FROM @DataTableParameter 
   ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
   
   -- do whatever needed, apply any logic, call stored procedures, etc.
   UPDATE @DataTable
   SET notes = REPLACE(notes, CONCAT('{{param = "', @parameterid, '"}}'), @value);

   SET @RowCount -= 1;
END;

-- test
SELECT * FROM @DataTable;

Output

 ---- -------------------------------------------------------------- 
| id |                            notes                             |
 ---- -------------------------------------------------------------- 
|  1 | Organization                                                 |
|  1 | develop document disseminate to apple doc. and   google doc. |
|  2 | develop document  to facebook doc.                           |
|  2 | test                                                         |
 ---- --------------------------------------------------------------