add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tempcust (
id int,
name nvarchar(50),
amount nvarchar(50),
groupid int
)

insert into tempcust (id,name,amount)
values
(1, 'Bob Smith', '$50.12'),
(2, 'Bob Smith', '$12.33' ),
(3, 'Bob Smith', '$33.12' ),
(4, 'Bob Smith', '$14.86' ),
(5, 'Bob Smith', '$6.36' ),
(6, 'Bob Smith', '$2.14'),
(7, 'Bob Smith', '$10.64'),
(8, 'Bob Smith', '$19.14'),
(9, 'Bob Smith', '$27.64'),
(10, 'Bob Smith', '$36.14'),
(11, 'Bob Smith', '$44.64'),
(12, 'Bob Smith', '$53.14'),
(13, 'Jane Doe', '$14.86' ),
(14, 'Jane Doe', '$6.36'),
(15, 'Jane Doe', '$2.14'),
(16, 'Jane Doe', '$10.64'),
(17, 'Jane Doe', '$19.14'),
(18, 'Jane Doe','$27.64'),
(19, 'Jane Doe', '$36.14'),
(20, 'Kylie Robinson', '$5.00') ,
(21, 'Kylie Robinson', '$6.00' ),
(22, 'Kylie Robinson', '$7.00' ),
(23, 'Kylie Robinson', '$8.00' ),
(24, 'Kylie Robinson', '$9.00' );
24 rows affected
select c.*, dense_rank() over (order by min_grp_id) as groupid
from (select c.*, min(id) over (partition by name, within_name_groupId) as min_grp_id
from (select c.*,
lag(name) over (order by name) as prev_name,
(1 + (row_number() over (partition by name order by id) - 1) / 5
) as within_name_groupId
from tempcust c
) c
) c
order by id;
id name amount groupid prev_name within_name_groupId min_grp_id groupid
1 Bob Smith $50.12 null null 1 1 1
2 Bob Smith $12.33 null Bob Smith 1 1 1
3 Bob Smith $33.12 null Bob Smith 1 1 1
4 Bob Smith $14.86 null Bob Smith 1 1 1
5 Bob Smith $6.36 null Bob Smith 1 1 1
6 Bob Smith $2.14 null Bob Smith 2 6 2
7 Bob Smith $10.64 null Bob Smith 2 6 2
8 Bob Smith $19.14 null Bob Smith 2 6 2
9 Bob Smith $27.64 null Bob Smith 2 6 2
10 Bob Smith $36.14 null Bob Smith 2 6 2
11 Bob Smith $44.64 null Bob Smith 3 11 3
12 Bob Smith $53.14 null Bob Smith 3 11 3
13 Jane Doe $14.86 null Bob Smith 1 13 4
14 Jane Doe $6.36 null Jane Doe 1 13 4
15 Jane Doe $2.14 null Jane Doe 1 13 4
16 Jane Doe $10.64 null Jane Doe 1 13 4
17 Jane Doe $19.14 null Jane Doe 1 13 4
18 Jane Doe $27.64 null Jane Doe 2 18 5
19 Jane Doe $36.14 null Jane Doe 2 18 5
20 Kylie Robinson $5.00 null Jane Doe 1 20 6
21 Kylie Robinson $6.00 null Kylie Robinson 1 20 6
22 Kylie Robinson $7.00 null Kylie Robinson 1 20 6
23 Kylie Robinson $8.00 null Kylie Robinson 1 20 6
24 Kylie Robinson $9.00 null Kylie Robinson 1 20 6