<p>SQL Experts,</p>

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

<p>For example if I have the following data:</p>

<pre><code>ID|Name
01|Harry Johns
02|Adam Taylor
03|John Smith
04|John Smith
05|Bill Manning
06|John Smith
</code></pre>

<p>I need to display this:</p>

<pre><code>Harry Johns
Adam Taylor
John Smith (2)
Bill Manning
John Smith
</code></pre>

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

[{'answer_id': 21492, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 0, 'selected': False, 'text': "<p>For this particular case, all you need to do is group by the name and ask for the count, like this:</p>\n\n<pre><code>select Name, count(*)\nfrom MyTable\ngroup by Name\n</code></pre>\n\n<p>That'll get you the count for each name as a second column.</p>\n\n<p>You can get it all as one column by concatenating like this:</p>\n\n<pre><code>select Name + ' (' + cast(count(*) as varchar) + ')'\nfrom MyTable\ngroup by Name\n</code></pre>\n"}, {'answer_id': 21510, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': 1, 'selected': False, 'text': "<p>Well, this:</p>\n\n<pre><code>select Name, count(Id)\nfrom MyTable\ngroup by Name\n</code></pre>\n\n<p>will give you this:</p>\n\n<pre><code>Harry Johns, 1\nAdam Taylor, 1\nJohn Smith, 2\nBill Manning, 1\n</code></pre>\n\n<p>and this (MS SQL syntax):</p>\n\n<pre><code>select Name +\n    case when ( count(Id) &gt; 1 ) \n         then ' ('+cast(count(Id) as varchar)+')' \n         else ''\n    end\nfrom MyTable\ngroup by Name\n</code></pre>\n\n<p>will give you this:</p>\n\n<pre><code>Harry Johns\nAdam Taylor\nJohn Smith (2)\nBill Manning\n</code></pre>\n\n<p>Did you actually want that other John Smith on the end of your results?</p>\n\n<p>EDIT: Oh I see, you want consecutive runs grouped. In that case, I'd say you need a cursor or to do it in your program code.</p>\n"}, {'answer_id': 21545, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 3, 'selected': True, 'text': "<p>Try this:</p>\n\n<pre><code>select n.name, \n    (select count(*) \n     from myTable n1\n     where n1.name = n.name and n1.id &gt;= n.id and (n1.id &lt;=\n        (\n        select isnull(min(nn.id), (select max(id) + 1 from myTable))\n        from myTable nn\n        where nn.id &gt; n.id and nn.name &lt;&gt; n.name\n        )\n     ))\nfrom myTable n\nwhere not exists (\n   select 1\n   from myTable n3\n   where n3.name = n.name and n3.id &lt; n.id and n3.id &gt; (\n            select isnull(max(n4.id), (select min(id) - 1 from myTable))\n            from myTable n4\n            where n4.id &lt; n.id and n4.name &lt;&gt; n.name\n            )\n)\n</code></pre>\n\n<p>I think that'll do what you want. Bit of a kludge though.</p>\n\n<p>Phew! After a few edits I think I have all the edge cases sorted out.</p>\n"}, {'answer_id': 21555, 'author': 'Leon Bambrick', 'author_id': 49, 'author_profile': 'https://Stackoverflow.com/users/49', 'pm_score': 2, 'selected': False, 'text': "<p>I hate cursors with a passion... but here's a dodgy cursor version...</p>\n\n<pre><code>Declare @NewName Varchar(50)\nDeclare @OldName Varchar(50)\nDeclare @CountNum int\nSet @CountNum = 0\n\nDECLARE nameCursor CURSOR FOR \nSELECT Name\nFROM NameTest\nOPEN nameCursor\n\nFETCH NEXT FROM nameCursor INTO @NewName\n\n  WHILE @@FETCH_STATUS = 0 \n\n    BEGIN\n\n      if @OldName &lt;&gt; @NewName\n      BEGIN\n         Print @OldName + ' (' + Cast(@CountNum  as Varchar(50)) + ')'\n         Set @CountNum = 0\n      END\n      SELECT @OldName = @NewName\n      FETCH NEXT FROM nameCursor INTO @NewName\n      Set @CountNum = @CountNum + 1\n\n    END\nPrint @OldName + ' (' + Cast(@CountNum  as Varchar(50)) + ')'\n\nCLOSE nameCursor\nDEALLOCATE nameCursor\n</code></pre>\n"}, {'answer_id': 21561, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 1, 'selected': False, 'text': "<p>How about this:</p>\n\n<pre><code>declare @tmp table (Id int, Nm varchar(50));\n\ninsert @tmp select 1, 'Harry Johns';\ninsert @tmp select 2, 'Adam Taylor';\ninsert @tmp select 3, 'John Smith';\ninsert @tmp select 4, 'John Smith';\ninsert @tmp select 5, 'Bill Manning';\ninsert @tmp select 6, 'John Smith';\n\nselect * from @tmp order by Id;\n\nselect Nm, count(1) from \n(\nselect Id, Nm, \n    case when exists (\n        select 1 from @tmp t2 \n        where t2.Nm=t1.Nm \n        and (t2.Id = t1.Id + 1 or t2.Id = t1.Id - 1)) \n        then 1 else 0 end as Run\nfrom @tmp t1\n) truns group by Nm, Run\n</code></pre>\n\n<p>[Edit] That can be shortened a bit</p>\n\n<pre><code>select Nm, count(1) from (select Id, Nm, case when exists (\n        select 1 from @tmp t2 where t2.Nm=t1.Nm \n        and abs(t2.Id-t1.Id)=1) then 1 else 0 end as Run\nfrom @tmp t1) t group by Nm, Run\n</code></pre>\n"}, {'answer_id': 21768, 'author': 'Jon Erickson', 'author_id': 1950, 'author_profile': 'https://Stackoverflow.com/users/1950', 'pm_score': 2, 'selected': False, 'text': "<p>My solution just for kicks (this was a fun exercise), no cursors, no iterations, but i do have a helper field</p>\n\n<pre><code>-- Setup test table\nDECLARE @names TABLE    (\n                        id      INT                 IDENTITY(1,1),\n                        name    NVARCHAR(25)        NOT NULL,\n                        grp     UNIQUEIDENTIFIER    NULL\n                        )\n\nINSERT @names (name)\nSELECT 'Harry Johns'    UNION ALL \nSELECT 'Adam Taylor'    UNION ALL\nSELECT 'John Smith'     UNION ALL\nSELECT 'John Smith'     UNION ALL\nSELECT 'Bill Manning'   UNION ALL\nSELECT 'Bill Manning'   UNION ALL\nSELECT 'Bill Manning'   UNION ALL\nSELECT 'John Smith'     UNION ALL\nSELECT 'Bill Manning'   \n\n-- Set the first id's group to a newid()\nUPDATE      n\nSET         grp = newid()\nFROM        @names n\nWHERE       n.id = (SELECT MIN(id) FROM @names)\n\n-- Set the group to a newid() if the name does not equal the previous\nUPDATE      n\nSET         grp = newid()\nFROM        @names n\nINNER JOIN  @names b\n        ON  (n.ID - 1) = b.ID\n        AND ISNULL(b.Name, '') &lt;&gt; n.Name\n\n-- Set groups that are null to the previous group\n-- Keep on doing this until all groups have been set\nWHILE (EXISTS(SELECT 1 FROM @names WHERE grp IS NULL))\nBEGIN\n    UPDATE      n\n    SET         grp = b.grp\n    FROM        @names n\n    INNER JOIN  @names b\n            ON  (n.ID - 1) = b.ID\n            AND n.grp IS NULL\nEND\n\n-- Final output\nSELECT      MIN(id)     AS id_start,\n            MAX(id)     AS id_end,\n            name,\n            count(1)    AS consecutive\nFROM        @names\nGROUP BY    grp, \n            name\nORDER BY    id_start\n\n/*\nResults:\n\nid_start    id_end  name            consecutive\n1           1       Harry Johns     1\n2           2       Adam Taylor     1\n3           4       John Smith      2\n5           7       Bill Manning    3\n8           8       John Smith      1\n9           9       Bill Manning    1\n*/\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/21489', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2281/']