How to loop concat in SQL?

Question

I am rather new at sql and got stuck with an issue where I wouldn't even know where to start solving it. That's why I am here.

Here is how my setup looks like (I simplified it a bit):

Columns of Calculation table:

• Article_ID
• Version
• Comment

Columns of temp table #needs_comment:

• Article_ID
• Calculation_Version
• Material_Number
• Material_Price_Old
• Material_Price_New

Sample content for #needs_comment:

+------------+---------------------+-----------------+--------------------+----------------------+
| Article_ID | Calculation_Version | Material_Number | Material_Price_Old | Material_Price_New   |
+------------+---------------------+-----------------+--------------------+----------------------+
|          1 |                   4 | Mat400          |                 20 |                   30 |
|          1 |                   4 | Mat500          |                 50 |                   60 |
|          2 |                   3 | Mat200          |                 30 |                   40 |
+------------+---------------------+-----------------+--------------------+----------------------+

My UPDATE statement:

UPDATE Calculation AS c
SET c.Comment = CONCAT('Fabric «', nc.Material_Number, '» price change from ', nc.Material_Price_Old, ' to ', nc.Material_Price_New, '.')

FROM Calculation
JOIN #needs_comment AS nc ON nc.Article_ID = c.Article_ID AND nc.Calculation_Version = c.Version

I want to automatically insert a comment into a calculation table, if the price of one or more materials used in a specific calculation has changed. Therefor I already created all the temporary tables I need to get the right data (ie. old material price, new material price, when was the calculation last updated and so on..).

My Update statement itself works. It updates the field comment in the calculation table with the information accordingly. But it only works if only one material has changed pricing. If there are more than one materials with different prizing for the same calculation, it only inserts one comment for one of the changes. But I want it insert a comment for each material changed. Unfortunately I don't understand how I can loop the comment for each of the changes.

This is how the Calculation table looks like after the update statement has run:

+------------+---------+---------------------------------------------+
| Article_ID | Version |                   Comment                   |
+------------+---------+---------------------------------------------+
|          1 |       4 | Fabric «Mat400» price change from 20 to 30. |
|          2 |       3 | Fabric «Mat200» price change from 30 to 40. |
+------------+---------+---------------------------------------------+

This is how i would like it to look like:

+------------+---------+-----------------------------------------------------------------------------------------+
| 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.                                             |
+------------+---------+-----------------------------------------------------------------------------------------+

Thank you very much for every bit of advise!

Answer 1

Here is a way to do this in sql server, which looks like the database you are using(not 100% sure though).

Using string_agg function which is available in all latesst versions of sql server, i concatenate multiple prices and materials into a single column called comment_concat.

Then i use the update as follows which updates the comment column of calculation table using the comment_concat.

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


+------------+---------+-----------------------------------------------------------------------------------+
| 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                                          |
+------------+---------+-----------------------------------------------------------------------------------+

db fiddle link with working example

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e05d1e4de26da1d186de4e3d381d86ec