Create table for store raw data:
CREATE TABLE [dbo].[raw_data]( [ID] [int] IDENTITY(1,1) NOT NULL, [first_data] [int] NULL, [next_counts] [nvarchar](max) NULL, PRIMARY KEY (ID) );
Create table for processed data:
CREATE TABLE [dbo].[SplittedAndProcessedData]( [ID] [int] IDENTITY(1,1) NOT NULL, [count] [int] NULL, PRIMARY KEY (ID) );
Now store raw data:
INSERT INTO [dbo].[raw_data] ( [raw_data].[first_data] ,[raw_data].[next_counts] ) VALUES ( 8500 /*first_data*/ ,'10,2,0,95,15' /*next_counts*/ )
I am turning a comma separated string into individual rows:
SELECT Splitted_Row.value FROM [dbo].[raw_data] CROSS APPLY STRING_SPLIT ([raw_data].[next_counts], ',') Splitted_Row
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:
- ID(1) = 8500 –first data
- ID(2) = 8500 + 10 = 8510
- ID(3) = 8510 + 2 = 8512
- ID(4) = 8512 + 0 = 8512
- ID(5) = 8512 + 95 = 8607
- 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.
-- INSERT INTO SplittedAndProcessedData ([count]) SELECT SUM(data) OVER (ORDER BY rn) AS [count] FROM ( SELECT -1 AS rn, first_data AS data FROM raw_data r UNION ALL SELECT CONVERT(int, j.[key]), TRY_CONVERT(int, j.value) FROM raw_data r CROSS APPLY OPENJSON(CONCAT('[', r.next_counts, ']')) j ) t
Result (form the SELECT
part of the statement):
count ----- 8500 8510 8512 8512 8607 8622