DECLARE @TableA TABLE (id_prod int, key_compos  varchar(100), TxtMatiereCompo_en nvarchar(500))
INSERT INTO @TableA 
SELECT 1019, '2009111916447465', NULL union all
SELECT 1025, '20091119164545691', NULL union all
SELECT 1030, '20091119164517627', NULL union all
SELECT 1039, '20091119164621171', NULL

DECLARE @tabB TABLE (pourcent int, id_compos varchar(100), matiere_en varchar(50))
INSERT INTO @tabB
SELECT 67, '2009111916447465', 'Aluminum'  union all
SELECT 22, '2009111916447465', 'Wood' union all
SELECT 11, '2009111916447465', 'Bamboo'

;WITH CTE AS 
(
	SELECT a.key_compos, b.matiere_en, b.pourcent
	FROM @TableA a JOIN @tabB b ON a.key_compos = b.id_compos
),
cte1 AS 
(
	SELECT c.key_compos, STUFF((SELECT ',' + CAST(s.pourcent as varchar(5))+ '% '+ s.matiere_en FROM CTE s WHERE s.key_compos = c.key_compos FOR XML PATH('')),1,1,'') AS CSV
	FROM CTE c
	GROUP BY c.key_compos
)
UPDATE t1
SET TxtMatiereCompo_en = csv
FROM @TableA t1 
JOIN cte1 ON t1.key_compos = cte1.key_compos

SELECT * FROM @TableA

EDIT: Simplified solution is as follows

;WITH CTE AS 
(
	SELECT a.key_compos, b.matiere_en, b.pourcent, a.TxtMatiereCompo_en
	FROM @TableA a JOIN @tabB b ON a.key_compos = b.id_compos
)
UPDATE extern
SET extern.TxtMatiereCompo_en =  LEFT(TxtMat , LEN(TxtMat )-1)
FROM CTE AS extern
CROSS APPLY
(
    SELECT CAST(intern.pourcent as varchar(5))+ '% '+ intern.matiere_en + ','
    FROM CTE AS intern
    WHERE extern.key_compos = intern.key_compos
    FOR XML PATH('')
) pre_trimmed (TxtMat);

SELECT * FROM @TableA

quote:

---

Originally posted by Lesombrero

Thank you so much Bandi. Very helpful.

---

DECLARE @TableA TABLE (id_prod int, key_compos  varchar(100), TxtMatiereCompo_en nvarchar(500))
INSERT INTO @TableA 
SELECT 1019, '2009111916447465', NULL union all
SELECT 1025, '20091119164545691', NULL union all
SELECT 1030, '20091119164517627', NULL union all
SELECT 1039, '20091119164621171', NULL

DECLARE @tabB TABLE (pourcent int, id_compos varchar(100), matiere_en varchar(50))
INSERT INTO @tabB
SELECT 67, '2009111916447465', 'Aluminum'  union all
SELECT 22, '2009111916447465', 'Wood' union all
SELECT 11, '2009111916447465', 'Bamboo'


UPDATE ta
SET ta.TxtMatiereCompo_en = STUFF((SELECT ',' + CAST(pourcent AS varchar(10)) + '% ' + CAST(matiere_en AS varchar(30)) FROM @tabB WHERE id_compos = ta.key_compos FOR XML PATH('')),1,1,'')
FROM @TableA ta


SELECT * FROM @TableA