title: Count Number of Consecutive Occurrence of values in Table
body: <p>I have below table </p>
  
  <pre><code>create table #t (Id int, Name char)
  
  insert into #t values
  (1, 'A'),
  (2, 'A'),
  (3, 'B'),
  (4, 'B'),
  (5, 'B'),
  (6, 'B'),
  (7, 'C'),
  (8, 'B'),
  (9, 'B')
  </code></pre>
  
  <p>I want to count consecutive values in name column</p>
  
  <pre><code>+------+------------+
  | Name | Repetition |
  +------+------------+
  | A    |          2 |
  | B    |          4 |
  | C    |          1 |
  | B    |          2 |
  +------+------------+
  </code></pre>
  
  <p>The best thing I tried is:</p>
  
  <pre><code>select Name
  , COUNT(*) over (partition by Name order by Id) AS Repetition
  from #t
  order by Id
  </code></pre>
  
  <p>but it doesn't give me expected result</p>