Grouping Data Runs

Question

SQL experts,

Is there an efficient way to group data runs with SQL?
Or it will process data in code more efficiently.

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 problems formatting the data with the built-in html table, with which it worked in the preview, but not on the final display.

Answer 1

Try the following:

 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 you will do what you want. A bit of a fang, though.

Phew! After a few changes, I think I have all the cases associated with ribs.

Answer 2

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

 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 

Answer 3

My solution is just for beats (it was a fun exercise), no cursors, no iterations, but I have an auxiliary field

 -- Setup test table DECLARE @names TABLE ( id INT IDENTITY(1,1), name NVARCHAR(25) NOT NULL, grp UNIQUEIDENTIFIER NULL ) INSERT @names (name) SELECT 'Harry Johns' UNION ALL SELECT 'Adam Taylor' UNION ALL SELECT 'John Smith' UNION ALL SELECT 'John Smith' UNION ALL SELECT 'Bill Manning' UNION ALL SELECT 'Bill Manning' UNION ALL SELECT 'Bill Manning' UNION ALL SELECT 'John Smith' UNION ALL SELECT 'Bill Manning' -- Set the first id group to a newid() UPDATE n SET grp = newid() FROM @names n WHERE n.id = (SELECT MIN(id) FROM @names) -- Set the group to a newid() if the name does not equal the previous UPDATE n SET grp = newid() FROM @names n INNER JOIN @names b ON (n.ID - 1) = b.ID AND ISNULL(b.Name, '') <> n.Name -- Set groups that are null to the previous group -- Keep on doing this until all groups have been set WHILE (EXISTS(SELECT 1 FROM @names WHERE grp IS NULL)) BEGIN UPDATE n SET grp = b.grp FROM @names n INNER JOIN @names b ON (n.ID - 1) = b.ID AND n.grp IS NULL END -- Final output SELECT MIN(id) AS id_start, MAX(id) AS id_end, name, count(1) AS consecutive FROM @names GROUP BY grp, name ORDER BY id_start /* Results: id_start id_end name consecutive 1 1 Harry Johns 1 2 2 Adam Taylor 1 3 4 John Smith 2 5 7 Bill Manning 3 8 8 John Smith 1 9 9 Bill Manning 1 */ 

Answer 4

Well it:

 select Name, count(Id) from MyTable group by Name 

will provide you with the following:

 Harry Johns, 1 Adam Taylor, 1 John Smith, 2 Bill Manning, 1 

and this (MS SQL syntax):

 select Name + case when ( count(Id) > 1 ) then ' ('+cast(count(Id) as varchar)+')' else '' end from MyTable group by Name 

will provide you with the following:

 Harry Johns Adam Taylor John Smith (2) Bill Manning 

Do you really want another John Smith at the end of your results?

EDIT: Oh, I see you want consecutive runs to be grouped. In this case, I would say that you need a cursor or do it in the program code.

Answer 5

How about this:

 declare @tmp table (Id int, Nm varchar(50)); insert @tmp select 1, 'Harry Johns'; insert @tmp select 2, 'Adam Taylor'; insert @tmp select 3, 'John Smith'; insert @tmp select 4, 'John Smith'; insert @tmp select 5, 'Bill Manning'; insert @tmp select 6, 'John Smith'; select * from @tmp order by Id; select Nm, count(1) from ( select Id, Nm, case when exists ( select 1 from @tmp t2 where t2.Nm=t1.Nm and (t2.Id = t1.Id + 1 or t2.Id = t1.Id - 1)) then 1 else 0 end as Run from @tmp t1 ) truns group by Nm, Run 

[Edit] This may be shortened a bit

 select Nm, count(1) from (select Id, Nm, case when exists ( select 1 from @tmp t2 where t2.Nm=t1.Nm and abs(t2.Id-t1.Id)=1) then 1 else 0 end as Run from @tmp t1) t group by Nm, Run 

Answer 6

In this particular case, all you have to do is group by name and request an invoice, for example:

 select Name, count(*) from MyTable group by Name 

This will give you an account for each name as a second column.

You can get everything as one column by combining this:

 select Name + ' (' + cast(count(*) as varchar) + ')' from MyTable group by Name