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
Utilizes WITH
to create a table similar to the reference one but using ROW_NUMBER
to generate the column sequencia
;
Calculate the grouping using the record with sequencia
1 to define the column agrupamento
as 0 as anchorage;
Takes subsequent records by adding 1 to the column agrupamento
if you change your value on col2
;
Performs the SUM
with the records generated and properly organized.
Observing: If there is an unaware column that can be used for sorting, just replace instead of (SELECT 0)
in the ORDER BY
of ROW_NUMBER
.
FORM 2:
Use a cursor
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;
WITH
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 run repeatedly to return subsets of data until the complete result is obtained.
GROUP BY
Groups a set of selected rows into a set of summary rows by the values of one or more columns or expressions in the SQL Server 2014
. A row is returned to each group. The aggregation functions in the list of <seleção>
of the clause SELECT
provide information about each group instead of individual lines.
ROW_NUMBER
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 on SQL Fiddle.
With a select it does not. Unless you have a column that defines how these sums should be grouped.
– Reginaldo Rigo
Try to solve using cursor.
– Marcell Alves
You can do an abuse somersault
ROW_NUMBER()
and Ctes, but for that you would have to define a column with the "canonical" order of the lines, sinceROW_NUMBER()
requires a clauseOVER
(which, in turn, requires a clauseORDER BY
)...– Wtrmute
@Pedro, what is the version and edition of SQL Server?
– José Diz
@Josédiz, this is 2008
– user26858