1
create table #t (Id int, Name nvarchar(max))    

insert into #t 
values (1, 'ABC'), (2, 'ABC'), (3, 'BBB'), (4, 'BBB'),  
       (5, 'BBB'), (6, 'BBB'), (7, 'CCC'), (8, 'BBB'),  
       (9, 'BBB'), (10, 'ABC')

I want to count values in name column if there are only in sequence form how I get this result as shown in table below: 

| Name | Repetition |   
('ABC'),    2
('ABC'),    2
('BBB'),    4
('BBB'),    4
('BBB'),    4
('BBB'),    4
('CCC'),    1
('BBB'),    2
('BBB'),    2
('ABC'),    1
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Envision Inc.
  • 13
  • 3

2 Answers2

3

Try this multiple cte solution:

;with name_change as (
  select *, case when name=lag(name, 1) over (order by id) then 0 else 1 end as indicator 
  from t
), name_group as (
  select name, sum(indicator) over (order by id) as g 
  from name_change
)
select name, count(name) over (partition by g) from name_group

Not sure about the efficiency though, but I think it gives what you need...

SQLFiddle

Psidom
  • 209,562
  • 33
  • 339
  • 356
  • thanks, we r working in sql server 2008 r2 and lag function is not available . please provide alternative query. we want same result that u provided previous query – Envision Inc. Dec 29 '17 at 08:05
0

You can also try gaps and islands type approach of counting consecutive rows like below

See live demo

select 
    Name, 
    Repetition= count(1) over(  partition by r,Name )
from 
(
    select 
    *, 
    r=id-row_number() over(partition by Name order by Id) 
    from #t
 ) t
order by id
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • Thanks but Please check .. wrong result found. create table #t (Id int, Name nvarchar(max)) insert into #t values (1, 'ABC'), (2, 'ABC'), (3, 'BBB'), (4, 'BBB'), (5, 'BBB'), (6, 'BBB'), (7, 'CCC'), (8, 'BBB'), (9, 'BBB'), (10,'ABC'), (11,'ABC'), (12,'ABC'), (13,'ABC'), (14,'CCC'), (15,'ABC'), (16,'ABC'), (17,'CCC'), (18,'GGG'), (19,'FFF'), (20,'GGG'), (21,'AAA') select Name, Repetition= count(1) over( partition by r ) from ( select *, r=id-row_number() over(partition by Name order by Id) from #t ) t order by id – Envision Inc. Dec 29 '17 at 08:33
  • Please check 17,18,19,20 row – Envision Inc. Dec 29 '17 at 08:47
  • @EnvisionInc. Thank you for pointing that out; answer updated and corrected – DhruvJoshi Dec 29 '17 at 11:13