SQL Sequential Grouping and strings for sequence gaps

Question

I am trying to generate a specific string based on the following data using SQL 2012

| Id | Activity | Year | 
|----|----------|------|
| 01 |  AAAAA   | 2008 |
| 01 |  AAAAA   | 2009 |
| 01 |  AAAAA   | 2010 |
| 01 |  AAAAA   | 2012 |
| 01 |  AAAAA   | 2013 |
| 01 |  AAAAA   | 2015 |
| 01 |  BBBBB   | 2014 |
| 01 |  BBBBB   | 2015 |

With the result needing to look like;

| 01 |  AAAAA   | 2008-2010, 2012-2013, 2015 |
| 01 |  BBBBB   | 2014-2015                  |

Any ideas on how to achieve this would be greatly appreciated.

Answer 1

Use ROW_NUMBER to group the contiguous years and FOR XML PATH('') for string concatenation.

SQL Fiddle

WITH Cte AS(
    SELECT *,
        grp = year - ROW_NUMBER() OVER(PARTITION BY id, activity ORDER BY year)
    FROM tbl
)
SELECT 
    id,
    activity,
    x.years
FROM Cte c
CROSS APPLY(
    SELECT STUFF((
        SELECT ', ' + CONVERT(VARCHAR(4), MIN(year)) +
            CASE 
                WHEN MIN(year) <> MAX(year) THEN '-' + CONVERT(VARCHAR(4), MAX(year))
                ELSE ''
            END
        FROM Cte
        WHERE
            id = c.id
            ANd activity = c.activity
        GROUP BY id, activity, grp
        FOR XML PATH('')
    ), 1, 2, '')
)x(years)
GROUP BY id, activity, x.years

RESULT:

| id | activity |                      years |
|----|----------|----------------------------|
| 01 |    AAAAA | 2008-2010, 2012-2013, 2015 |
| 01 |    BBBBB |                  2014-2015 |

Answer 2

You can do it by using XML path (for concatenating group values) and grouping by id and anctivity:

MS SQL Server Schema Setup:

create table tbl (id varchar(2),activity varchar(10),year int);

insert into tbl values
( '01' ,'AAAAA', 2008 ),
( '01' ,'AAAAA', 2009 ),
( '01' ,'AAAAA', 2010 ),
( '01' ,'AAAAA', 2012 ),
( '01' ,'AAAAA', 2013 ),
( '01' ,'AAAAA', 2015 ),
( '01' ,'BBBBB', 2014 ),
( '01' ,'BBBBB', 2015 )

Query:

select
     id, activity,
     stuff(
         (select distinct ',' + cast(year as varchar(4))
          from tbl
          where id = t.id and activity=t.activity
          for xml path (''))
          , 1, 1, '')  as years
from tbl AS t
group by id,activity

Results:

| id | activity |                         years |
|----|----------|-------------------------------|
| 01 |    AAAAA | 2008,2009,2010,2012,2013,2015 |
| 01 |    BBBBB |                     2014,2015 |

---

Edit after comments and noticing well to the desired output:

if you want to also group the consecutive like 2008-2009 then you need an extra grouping (the difference of year and rank in each group will give you a distinct nested group):

Query:

with cte1 as
(
  select r = year - (rank() over(partition by id,activity 
                     order by year)),
  id,activity,year from tbl
)
,cte2 as 
(
  select
     id, activity, cast(min(year) as varchar(4)) +
          case when min(year)<>max(year) 
               then '-' + cast(max(year) as varchar(4))
          else '' end as years
  from cte1 
  group by r,id,activity
 )
 select
         id, activity,
         stuff(
             (select distinct ',' + years
              from cte2
              where id = t.id and activity=t.activity
              for xml path (''))
              , 1, 1, '')  as years
    from cte2 AS t
    group by id,activity

Results:

| id | activity |                    years |
|----|----------|--------------------------|
| 01 |    AAAAA | 2008-2010,2012-2013,2015 |
| 01 |    BBBBB |                2014-2015 |