SQL Query for Grouping the results based on sequence

I have a table like this:

ID  Seq  Amt
1   1    500
1   2    500
1   3    500
1   5    500
2   10   600
2   11   600
3   1    700
3   3    700

I want to group the continuous sequence numbers into a single row like this:

ID  Start  End  TotalAmt
1   1      开发者_如何学C3    1500
1   5      5    500
2   10     11   1200
3   1      1    700
3   3      3    700

Please help to achieve this result.

WITH numbered AS (
  SELECT
    ID, Seq, Amt,
    SeqGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Seq) - Seq
  FROM atable
)
SELECT
  ID,
  Start = MIN(Seq),
  [End] = MAX(Seq),
  TotalAmt = SUM(Amt)
FROM numbered
GROUP BY ID, SeqGroup
ORDER BY ID, Start
;

Well, there's perhaps a more elegant way to do this (something hints at me that there is), but here's an approach that will work if you're using a version of SQL Server that accepts common table expressions:

use Tempdb
go

create table [Test]
(
    [id] int not null,
    [Seq] int not null,
    [Amt] int not null
)

insert into [Test] values
(1, 1, 500),
(1, 2, 500),
(1, 3, 500),
(1, 5, 500),
(2, 10, 600),
(2, 11, 600),
(3, 1, 700),
(3, 3, 700)

;with
lower_bound as (
    select *
      from Test
     where not exists (
        select *
          from Test as t1
         where t1.id = Test.id and t1.Seq = Test.Seq - 1
    )
),
upper_bound as (
    select *
      from Test
     where not exists (
        select *
          from Test as t1
         where t1.id = Test.id and t1.Seq = Test.Seq + 1
    )
),
bounds as (
    select id, (select MAX(seq) from lower_bound where lower_bound.id = upper_bound.id and lower_bound.Seq <= upper_bound.Seq) as LBound, Seq as Ubound
      from upper_bound
)
select Test.id, LBound As [Start], UBound As [End], SUM(Amt) As TotalAmt
  from Test
  join bounds
    on Test.id = bounds.id
   and Test.Seq between bounds.LBound and bounds.Ubound
 group by Test.id, LBound, UBound

drop table [Test]

This seems to work nicely. @breakingRows will contain all rows that break the sequence of id and seq (i.e. if id changes or if seq is not 1 more than the previous seq). With that table you can select all rows of such a sequence within @temp. I must add however that performance will probably be not all that good because of all the subqueries but you'll need to test to be sure.

declare @temp table (id int, seq int, amt int)
insert into @temp select 1, 1, 500
insert into @temp select 1, 2, 500
insert into @temp select 1, 3, 500
insert into @temp select 1, 5, 500
insert into @temp select 2, 10, 600
insert into @temp select 2, 11, 600
insert into @temp select 3, 1, 700
insert into @temp select 3, 3, 700

declare @breakingRows table (ctr int identity(1,1), id int, seq int)

insert into @breakingRows(id, seq)
select id, seq
from @temp t1 
where not exists 
    (select 1 from @temp t2 where t1.id = t2.id and t1.seq - 1 = t2.seq)
order by id, seq

select br.id, br.seq as start, 
       isnull ((select top 1 seq from @temp t2 
               where id < (select id from @breakingRows br2 where br.ctr = br2.ctr - 1) or 
                     (id = (select id from @breakingRows br2 where br.ctr = br2.ctr - 1) and
                      seq < (select seq from @breakingRows br2 where br.ctr = br2.ctr - 1))          
               order by id desc, seq desc),
               br.seq)
      as [end],
      (select SUM(amt) from @temp t1 where t1.id = br.id and 
        t1.seq < 
            isnull((select seq from @breakingRows br2 where br.ctr = br2.ctr - 1 and br.id = br2.id), 
                   (select max(seq) + 1 from @temp)) and 
        t1.seq >= br.seq)
from @breakingRows br
order by id, seq

Since Andriy has already posted the gold solution, here's my take using an UPDATE statement to get the result from a temp table, just for fun.

declare @tmp table (
    id int, seq int, amt money, start int, this int, total money,
    primary key clustered(id, seq))
;
insert @tmp
select *, start=seq, this=seq, total=convert(money,amt)
from btable
;
declare @id int, @seq int, @start int, @amt money
update @tmp
set 
    @amt = total = case when id = @id and seq = @seq+1 then @amt+total else amt end,
    @start = start = case when id = @id and seq = @seq+1 then @start else seq end,
    @seq = this = seq,
    @id = id = id
from @tmp
option (maxdop 1)
;
select id, start, max(this) [end], max(total) total
from @tmp
group by id, start
order by id, start

Notes:

• btable: your table name
• id int, seq int, amt money: expected columns in your table

Try following query.

select id, min(seq), max(seq), sum(amt) from table group by id

OOps, sorry, it is wrong query as you need sequence

SELECT Id, MIN(Seq) as Start, MAX(Seq) as End, SUM(Amount) as Total
FROM ( 
        SELECT t.*, Seq - ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Seq) Rn
        FROM [Table] t
    ) as T
GROUP BY Id, Rn
ORDER BY Id, MIN(Seq)