question

kiranmath avatar image
kiranmath asked

Tsql Grouping

Say I have a following Dataset ... **Data Set** Row Status 1 L 2 H 3 H 4 H 5 L 6 L 7 H 8 H 9 H Help me write a sql to get the following **Result** Status StartRow EndRow L 1 1 H 2 4 L 5 6 H 7 9
tsql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
This is a variation on the 'Gaps and Islands' problem. declare @Yourtable table ([Row] int, [status] char(1)) insert into @Yourtable select 1,'L' insert into @Yourtable select 2,'H' insert into @Yourtable select 3,'H' insert into @Yourtable select 4,'H' insert into @Yourtable select 5,'L' insert into @Yourtable select 6,'L' insert into @Yourtable select 7,'H' insert into @Yourtable select 8,'H' insert into @Yourtable select 9,'H' select [status], min([row]) as StartRow, max([row]) as EndRow from ( select [row], [status], row_number()over(order by [row]) - row_number()over(order by [status],[row]) as grp from @Yourtable ) Grouped group by [status], grp order by StartRow
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Kev this worked like magic.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.