Using your year - row_number() over, you could do an additional modulus calculation of 3 and then filter with 0.
Something like this:
with events as(
select
1 id,
'Arun' name,
2001 year
union all select 2, 'Arun', 2002
union all select 3, 'Arun', 2003
union all select 4, 'Arun', 2004
union all select 5, 'Arun', 2009
union all select 6, 'Arun', 2010
union all select 7, 'Arun', 2011
union all select 8, 'Bala', 2014
union all select 9, 'Bala', 2015
union all select 10, 'Bala', 2017
union all select 11, 'chan', 2014
union all select 12, 'chan', 2015
union all select 13, 'chan', 2018
union all select 14, 'Arun', 2006
union all select 15, 'Arun', 2005
union all select 16, 'Bala', 2018
union all select 17, 'Bala', 2020
union all select 18, 'Bala', 2019
union all select 22, 'Jim', 2009
union all select 21, 'Jim', 2010
union all select 20, 'Jim', 2011
), eventsGroup as (
select
e.*,
(year - row_number() over (partition by name order by year)) as grp
from events e
), eventsGroup3Year as (
select
*,
(row_number() over (partition by name, grp order by year))%3 as rowid3
from eventsGroup
)
select
row_number() over(order by name, year) id,
name,
convert(char(4), year-2)+' - '+convert(char(4), year) years
from eventsGroup3Year
where rowid3 = 0
order by
name,
year
;
It will probably not work if you have duplicate values,
lets say an additional row of 'Arun', 2003 - you will need to test that out.
Hope that helps out.
-----
Additional sql [for some reason this question has gotten stuck in my head :)]:
To get the start and end year in the same row/record, you could use the LAG analytic function (ms sql 2012 upwards).
Something like this:
with (
)
, LagYear as(
select
*,
lag(year, 1, 0) over (order by name, year) StartYear
from eventsGroup3Year
where rowid3 = 0 or rowid3 = 1
)
select
row_number() over(order by name, year) id,
name,
concat(StartYear, ' - ', year) Years
from LagYear where rowid3 = 0
;
LAG (Transact-SQL):
https://msdn.microsoft.com/en-us/library/hh231256.aspx[
^]