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

Id
1
2
3
4
8
9
14
15
16
17

Expected Output

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

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.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements