Substitute a column value with another columns foreign key value in SQL code

Question

I have trouble in writing a SQL query.

I have a data table with below columns.

DataTable 1

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

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   develop document disseminate     develop document disseminate to apple doc and google doc
     to{{param = "ac-1_prm_1"}} 
     and {{param = "ac-1_prm_2"}} 

 2   develop document                           develop document facebook doc 
     to {{param = "ac-1_prm_3"}}

Answer 1

Try this:

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

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 2

Hi， @Aditi Sharma
Welcome to the microsoft tsql Q&A forum!

As I answered in your latest question：

;WITH cteA AS  
(  
 SELECT P1.control_id,(SELECT VALUE+' and ' FROM DataTableParameter P2  
                       WHERE P1.control_id = P2.control_id  
				       FOR XML PATH ('')) AS 'Val'  
 FROM DataTableParameter P1  
),  
cteB AS  
(  
 SELECT control_id,SUBSTRING (Val,1, LEN(Val)-5 ) AS VALUE  
 FROM cteA  
)  
SELECT DISTINCT D.control_id,notes,  
    CASE   
	  WHEN CHARINDEX('{',notes) > 0  
	  THEN REPLACE (notes,  
	                SUBSTRING (notes,  
					           CHARINDEX('{',notes),  
							   LEN(notes)-CHARINDEX('{',notes)+1),   
					VALUE)   
	  ELSE notes  
	END AS final_notes  
FROM DataTable D  
JOIN cteB  
ON D.control_id = cteB.control_id  

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".