SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
 Mar  4 2020 00:59:26 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table calculation(article_id int, version int, comment varchar(200))
create table needs_comment(article_id int,calculation_version int, material_number varchar(50), material_price_old int, material_price_new int)
insert into needs_comment 
select 1,4,'Mat400',20,30 union all
select 1,4,'Mat500',50,60 union all
select 2,3,'Mat200',30,40 
3 rows affected

insert into calculation
select 1,4,'' union all
select 2,3,''
2 rows affected

select article_id
      ,calculation_version
      ,string_agg(concat('Fabric ',material_number,' price change from ',material_price_old,' to ',material_price_new),concat('.',CHAR(10))) as comment_concat
  from needs_comment
group by article_id
         ,calculation_version  
  


article_id
calculation_version
comment_concat


2
3
Fabric Mat200 price change from 30 to 40

1
4
Fabric Mat400 price change from 20 to 30.
Fabric Mat500 price change from 50 to 60


update calculation 
   set comment= nc.comment_concat
 from calculation c 
 join (select article_id
             ,calculation_version
             ,string_agg(concat('Fabric ',material_number,' price change from ',material_price_old,' to ',material_price_new),'.') as comment_concat
        from needs_comment
       group by article_id
                ,calculation_version 
      ) nc
   on c.article_id=nc.article_id
  and c.version=nc.calculation_version
2 rows affected

select *
  from calculation


article_id
version
comment


1
4
Fabric Mat400 price change from 20 to 30.Fabric Mat500 price change from 50 to 60

2
3
Fabric Mat200 price change from 30 to 40

article_id	calculation_version	comment_concat
2	3	Fabric Mat200 price change from 30 to 40
1	4	Fabric Mat400 price change from 20 to 30. Fabric Mat500 price change from 50 to 60