To solve the issue follow these steps:

1. Create a table with a JSON format column. For example, a table named Calculation with columns calculationNr, date, volume, and calculation.

2. Create a View using the following query to split the column containing JSON value and create separate fields as:

   Create View SplitView As SELECT c.generalCal, c.position, c.counter,
          JSON_VALUE(x.Value, '$. generalCal) as generalCal,
          JSON_VALUE(x.Value, '$. position) as position,
          JSON_VALUE(x.Value, '$. counter) as counter
   FROM calculations c
   CROSS APPLY OPENJSON(calcuation) as x
   

   This query will create separate fields for generalCal, position, and counter based on the JSON values in the calcuation column.

3. Connect to SQL server and import the created view.

4. You will get three separate fields as you want in your given simplified table.

5. This guide will help you to do following sum.

   SumOfValue	SumOfCounter1	SumOfCounter2
   150	1000	800
   40	25	88

Some other references:

• https://support.boldbi.com/kb/article/12540/how-to-split-json-value-and-create-separate-fields-in-sql-server