Skip to content
Advertisement

Loop in SQL and increase one field each time

Create table for store raw data:

Create table for processed data:

Now store raw data:

I am turning a comma separated string into individual rows:

Like this:

value
10
2
0
95
15

I want to insert into SplittedAndProcessedData table using a loop, like this:

ID count
1 8500
2 8510
3 8512
4 8512
5 8607
6 8622

Which means:

  1. ID(1) = 8500 –first data
  2. ID(2) = 8500 + 10 = 8510
  3. ID(3) = 8510 + 2 = 8512
  4. ID(4) = 8512 + 0 = 8512
  5. ID(5) = 8512 + 95 = 8607
  6. ID(6) = 8607 + 15 = 8622

Advertisement

Answer

You need a cumulative sum, but using STRING_SPLIT() to extract the substring by position is a tricky apporach. As is mentioned in the documentation, the output rows might be in any order and the order is not guaranteed to match the order of the substrings in the input string.

But you may try a JSON-based approach to parse the next_counts column. You need to transform the data into a valid JSON array (10,2,0,95,15 into [10,2,0,95,15]) and parse this array with OPENJSON(). The result from the OPENJSON() call is a table with columns key, value and type and the key column holds the 0-based index of the element in the specified array.

Result (form the SELECT part of the statement):

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement