question

shridhar avatar image
shridhar asked

show output in group wise in sql server

id val 1 1 2 1 3 1 4 0 5 1 6 1 7 0 8 1 i just want to receive id group by val and val=1 i want my output like minid maxid 1 3 5 6 8 8
sql-server-2008sqlgroup-bygrouping
2 comments
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.

id 1 2 3 4 5 6 7 8 val 1 1 0 1 1 0 1 1 user 11 11 12 13 13 13 12 13 output will be minid 1 4 7 8 maxid 2 5 7 8 user 11 13 12 13
0 Likes 0 ·
updated my answer below
0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
This is the 'islands' problem. For more detail see Itzik Ben-Gan's artcile : http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions declare @YourTable table (id int, val int); insert into @YourTable values ( 1 , 1 ), ( 2 , 1), ( 3 , 1), ( 4 , 0), ( 5 , 1), ( 6 , 1), ( 7 , 0), ( 8 , 1); with cte as ( select id, id - dense_rank()over (order by id) as grp from @YourTable where Val = 1 ) select min(id) as minid, max(id) as maxid from cte group by grp; returns minid maxid ----------- ----------- 1 3 5 6 8 8 (3 row(s) affected) ---- Now you've added user, simply add this to your grouping declare @YourTable table (id int, val int, [user] int); insert into @YourTable values ( 1 , 1,11), ( 2 , 1,11), ( 3 , 0,12), ( 4 , 1,13), ( 5 , 1,13), ( 6 , 0,13), ( 7 , 1,12), ( 8 , 1,13); with cte as ( select id, id - dense_rank()over (order by id) as grp, [user] from @YourTable where Val = 1 ) select min(id) as minid, max(id) as maxid, [User] from cte group by grp, [User] order by minid; returns minid maxid User ----------- ----------- ----------- 1 2 11 4 5 13 7 7 12 8 8 13 (4 row(s) affected)
3 comments
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.

Thanks but if their is below row are id val user 9 0 12 10 1 13 11 1 13 then result for user 13 will be minid maxid user 8 11 13
0 Likes 0 ·
But then that implies that id=9 is in the grouping for user 13, when it isn't. It also could imply that id=9 has a val=1, whch again it doesn't - are you sure this is what you want?
0 Likes 0 ·
Thanks Kev i solve this
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.