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!

I am not sure about using single update statement. Are you aware of cursor in Sql?. you may need to write logic in While loop to check duplication and concat accordingly. Refer - MSDN link here - learn.microsoft.com/en-us/sql/t-sql/language-elements/… — SSD
– SSD, Commented Aug 4, 2020 at 15:17

George Joseph · Accepted Answer · 2020-08-04 15:34:21Z

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

Thank you very much! This is exactly what I was looking for :-)

Collectives™ on Stack Overflow

How to loop concat in SQL?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related