SQL Sequential Grouping and String Breaks

Question

I am trying to create a specific row 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 | 

As a result, you need to look;

 | 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 continuous years and FOR XML PATH('') to concatenate strings.

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 this using an XML path (to combine group values) and grouping by id and activity:

Setting up the MS SQL Server schema :

 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 clearly notice the desired result:

if you also want to group sequential, like 2008-2009, then you need additional grouping (the difference of year and rank in each group will give you an excellent 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 | 

Answer 3

Since I am sql-2012, I changed the response to the genius @FelixPamittan message using CONCAT .

 ;WITH Cte AS( SELECT *, grp = year - ROW_NUMBER() OVER(PARTITION BY id, activity ORDER BY year) FROM #TEMP ) SELECT DISTINCT id, activity, (SELECT STUFF((SELECT ', ' + CONCAT(MIN(year), CASE WHEN MIN(year) <> MAX(year) THEN CONCAT('-', MAX(year)) ELSE '' END) FROM Cte WHERE id = c.id ANd activity = c.activity GROUP BY id, activity, grp FOR XML PATH('') ), 1, 2, '')) AS YEARS FROM Cte c