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
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
15 People are following this question.
