compare and sum in sequence with sql server

FORM 1:

The following query :

-- Gera uma coluna "sequencia" para ser utilizada de referência
WITH referencia AS (
  SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS sequencia,
         t.*
    FROM tabela t
),
-- Calcula a coluna "agrupamento"
agrupamento AS (
  -- Pega o primeiro para definir como "agrupamento" = 1
  SELECT ref.*,
         1 AS agrupamento  
    FROM referencia ref
   WHERE ref.sequencia = 1
  UNION ALL
  -- Pega os subsequentes e apenas adiciona 1 ao agrupamento anterior se tiver mudado a "col2"
  SELECT atu.*,
         CASE atu.col2
           WHEN ant.col2 THEN ant.agrupamento
           ELSE ant.agrupamento + 1
         END AS agrupamento
    FROM agrupamento ant
         INNER JOIN referencia atu ON atu.sequencia = ant.sequencia + 1
)
-- Agrupa e soma os resultados
SELECT agr.col1,
       agr.col2,
       SUM(agr.col3) AS col3
  FROM agrupamento agr
 GROUP BY agr.agrupamento,
          agr.col1,
          agr.col2

• Uses WITH to create a table similar to reference but using ROW_NUMBER to generate column sequencia ;

• Calculates collation by first using the record with% co_of% 1 to set the% col_de% column to 0 as anchor;

• Performs sequencia with records generated and properly organized.

Note: If there are any uninformed columns that can be used for sorting, just substitute instead of agrupamento in agrupamento col2 .

FORM 2 :

Use SUM to iterate the table and calculate the values by placing them in a variable table:

DECLARE @resultado TABLE(sequencia INT IDENTITY,
                         col1      VARCHAR(10),
                         col2      VARCHAR(10),
                         col3      INT);

DECLARE @col1             VARCHAR(10),
        @col2             VARCHAR(10),
        @col3             INT,
        @ultima_sequencia INT,
        @ultimo_col1      VARCHAR(10),
        @ultimo_col2      VARCHAR(10);

-- Percorre os registros da tarefa
DECLARE cursor_agrupamento CURSOR LOCAL FAST_FORWARD FOR
  SELECT t.col1,
         t.col2,
         t.col3
    FROM tabela t;
OPEN cursor_agrupamento
FETCH NEXT FROM cursor_agrupamento INTO @col1,
                                        @col2,
                                        @col3
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Caso não seja o mesmo "col1" ou não seja o mesmo "col2" do registro anterior insere um novo registro
  IF @col1 <> ISNULL(@ultimo_col1, 0) OR @col2 <> ISNULL(@ultimo_col2, 0)
  BEGIN
    INSERT INTO @resultado(col1, col2, col3)
                    VALUES(@col1, @col2, @col3);

    -- Guarda o último "IDENTITY" inserido
    SET @ultima_sequencia = SCOPE_IDENTITY();
  END
  ELSE
  BEGIN
    -- Atualiza o valor somando o valor do atual
    UPDATE r
       SET r.col3 = r.col3 + @col3
      FROM @resultado r
     WHERE r.sequencia = @ultima_sequencia;
  END;

  -- Atualiza os valores que serão verificados na próxima interação
  SET @ultimo_col1 = @col1;
  SET @ultimo_col2 = @col2;

  FETCH NEXT FROM cursor_agrupamento INTO @col1,
                                          @col2,
                                          @col3
END;
CLOSE cursor_agrupamento;
DEALLOCATE cursor_agrupamento;

SELECT r.col1,
       r.col2,
       r.col3
  FROM @resultado r
 ORDER BY r.sequencia;

  

(SELECT 0)

     

A CTE (common table expression) provides the significant advantage of being able to self-reference, thus creating a recursive CTE. A recursive CTE is one in which an initial CTE is executed repeatedly to return subsets of data until the complete result is obtained.

  

ORDER BY

     

Group a set of selected rows into a set of summary rows by the values of one or more columns or expressions in ROW_NUMBER . A row is returned for each group. The aggregate functions in the cursor list of the WITH clause provide information about each group instead of individual rows.

  

GROUP BY

     

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

See working in SQL Fiddle .