create table tab_a
(
from_a int,
to_a int,
name varchar(50)
)
insert into tab_a
select 1,5,'a' union all
select 6,12,'a' union all
select 13,15,'a' union all
select 18,20,'a' union all
select 22,35,'a' union all
select 40,45,'a' union all
select 46,50,'a'
;WITH CTE AS (
SELECT
ROW_NUMBER() OVER ( ORDER BY B.number)-B.number AS GROUPS,
A.*,
B.number
FROM #tab_a A
JOIN [master].dbo.spt_values B ON B.[type]='P' AND B.number BETWEEN A.from_a AND A.to_a
)
SELECT
MIN(number) AS from_a,
MAX(number) AS to_a
FROM CTE
GROUP BY GROUPS
ORDER BY 1
from_a to_a
----------- -----------
1 15
18 20
22 35
40 50
(4 row(s) affected)
WITH cte AS (
SELECT *,ta.from_a AS pid
FROM tab_a AS ta WHERE NOT EXISTS (SELECT 0 FROM tab_a AS a WHERE a.name=ta.name AND a.to_a+1=ta.from_a)
UNION ALL
SELECT ta.*,c.pid
FROM tab_a AS ta INNER JOIN cte AS c ON ta.name=c.name AND ta.from_a=c.to_a+1
)
SELECT MIN(from_a),MAX(to_a),name FROM cte GROUP BY pid,name
;WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY a.name ORDER BY a.from_a) AS RowNr
FROM #tab_a a
WHERE NOT EXISTS(SELECT * FROM #tab_a WHERE name = a.name AND a.to_a + 1 = from_a)
)
SELECT c.name,MIN(c.from_a),MAX(c.to_a)
FROM CTE a
LEFT JOIN CTE b ON a.RowNr -1 = b.RowNr
INNER JOIN #tab_a c ON c.from_a > ISNULL(b.from_a,0) AND c.to_a <= a.to_a
GROUP BY a.RowNr,c.name
