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.
|1-4, 8-9, 14-17|
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 --
The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to email@example.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 --
-- 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.
|☛ 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
Dipali Jaiswal said:
;with cte as
( Select Id ,( id- row_number()over (order by id)) as dif from CreateSequence)
,cte2 as (Select cast(min(id) as varchar) + ‘-‘ + cast(max(id) as varchar) num from cte group by dif)
Select STRING_AGG(num, ‘,’) from cte2
Pawan Kumar Khowal said:
Excellent. Thank you for your comment.