How join 2 table with calculation in the relation ID
WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) &gt; 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) &gt; 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) &gt; 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) &gt; 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) &gt; 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle (parenthesis in mappings removed).
PS. None Tb_Value.ID value must be a prefix of another Tb_Value.ID value. If false then update CHARINDEX checking expression accordingly.


oh maybe becase the VAL i have is VAL1 - VAL50 , because CHARINDEX() make the result like that ? example VAL9+VAL10 and the result VAL1,VAL9,VAL10 ? – Fikri

WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID + '+' Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) &gt; 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) &gt; 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) &gt; 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) &gt; 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) &gt; 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle