SQL Puzzle | Club Sequence Data(islands) puzzle

In this puzzle you have to club the sequence data together. For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE CreateSequence
(
	Id INT 
)
GO

INSERT INTO CreateSequence VALUES
(1 ),
(2 ),
(3 ),
(4 ),
(8 ),
(9 ),
(14),
(15),
(16),
(17)
GO

SELECT * FROM CreateSequence
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--


;WITH CTE AS
(
	SELECT CONCAT(MIN(Id),'-',MAX(Id)) Id
	FROM 
	(
		SELECT Id, SUM(a) OVER (Order BY Id) Grouper
		FROM  
		(
			SELECT * ,IIF(Id -  ISNULL(LAG(Id) OVER (ORDER BY Id),0)>1,1,0) a FROM 
			CreateSequence 
		) l
	)x GROUP BY Grouper
)
SELECT DISTINCT  
	  STUFF 
		((
			SELECT ', ' + Id
			FROM CTE a
			FOR XML PATH('')
		) ,1,2,'') 
		AS Ouptut
FROM CTE

--

Output-1

--                            

Ouptut
-------------------
1-4, 8-9, 14-17

(1 row affected)

--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Author Introduction: Pawan Khowal

Pawan is a SQL Server Expert. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Enjoy !!! Keep Learning

Http://MsbiSkills.com