Grouping runs of data

Question

SQL Experts,

Is there an efficient way to group runs of data together using SQL?
Or is it going to be more efficient to process the data in code.

For example if I have the following data:

ID|Name
01|Harry Johns
02|Adam Taylor
03|John Smith
04|John Smith
05|Bill Manning
06|John Smith

I need to display this:

Harry Johns
Adam Taylor
John Smith (2)
Bill Manning
John Smith

@Matt: Sorry I had trouble formatting the data using an embedded html table it worked in the preview but not in the final display.

Answer 1

Try this:

select n.name, 
    (select count(*) 
     from myTable n1
     where n1.name = n.name and n1.id >= n.id and (n1.id <=
        (
        select isnull(min(nn.id), (select max(id) + 1 from myTable))
        from myTable nn
        where nn.id > n.id and nn.name <> n.name
        )
     ))
from myTable n
where not exists (
   select 1
   from myTable n3
   where n3.name = n.name and n3.id < n.id and n3.id > (
            select isnull(max(n4.id), (select min(id) - 1 from myTable))
            from myTable n4
            where n4.id < n.id and n4.name <> n.name
            )
)

I think that'll do what you want. Bit of a kludge though.

Phew! After a few edits I think I have all the edge cases sorted out.

Answer 2

I hate cursors with a passion... but here's a dodgy cursor version...

Declare @NewName Varchar(50)
Declare @OldName Varchar(50)
Declare @CountNum int
Set @CountNum = 0

DECLARE nameCursor CURSOR FOR 
SELECT Name
FROM NameTest
OPEN nameCursor

FETCH NEXT FROM nameCursor INTO @NewName

  WHILE @@FETCH_STATUS = 0 

    BEGIN

      if @OldName <> @NewName
      BEGIN
         Print @OldName + ' (' + Cast(@CountNum  as Varchar(50)) + ')'
         Set @CountNum = 0
      END
      SELECT @OldName = @NewName
      FETCH NEXT FROM nameCursor INTO @NewName
      Set @CountNum = @CountNum + 1

    END
Print @OldName + ' (' + Cast(@CountNum  as Varchar(50)) + ')'

CLOSE nameCursor
DEALLOCATE nameCursor