SQL PUZZLE | How to Select/Generate the numbers between two numbers? | SQL Interview Question

In this puzzle you have to select the numbers between two numbers. Please check the sample input and expected output for more details

Sample Input

Id St Et
1 2 7
2 10 15
3 16 18

Expected Output

Id St Et Number
1 2 7 2
1 2 7 3
1 2 7 4
1 2 7 5
1 2 7 6
1 2 7 7
2 10 15 10
2 10 15 11
2 10 15 12
2 10 15 13
2 10 15 14
2 10 15 15
3 16 18 16
3 16 18 17
3 16 18 18

Script – DDL and INSERT Script

 ```-- CREATE TABLE Nums ( Id INT ,St INT ,Et INT ) GO INSERT INTO Nums VALUES (1,2,7), (2,10,15), (3,16,18) GO SELECT * FROM Nums GO -- ```

Rules/Restrictions

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

SOLUTION 1

 ```-- ;WITH SingleDigits(Number) AS ( SELECT Number FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(Number) ) ,Series AS ( SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number from SingleDigits as d1, SingleDigits as d2, SingleDigits as d3, SingleDigits as d4 ) SELECT * FROM Nums N INNER JOIN Series s ON s.Number >= N.St AND s.Number <= N.Et ORDER BY Id,Number -- ```

OUTPUT – 1

 ```-- Id St Et Number ----------- ----------- ----------- ----------- 1 2 7 2 1 2 7 3 1 2 7 4 1 2 7 5 1 2 7 6 1 2 7 7 2 10 15 10 2 10 15 11 2 10 15 12 2 10 15 13 2 10 15 14 2 10 15 15 3 16 18 16 3 16 18 17 3 16 18 18 (15 rows affected) -- ```

SOLUTION 2

 ```-- SELECT DISTINCT N.* , S.number FROM master..spt_values s INNER JOIN Nums N ON s.number >= N.St AND s.Number <= N.Et WHERE s.number > 0 ORDER BY Id,s.Number -- ```

OUTPUT

 ```-- Id St Et number ----------- ----------- ----------- ----------- 1 2 7 2 1 2 7 3 1 2 7 4 1 2 7 5 1 2 7 6 1 2 7 7 2 10 15 10 2 10 15 11 2 10 15 12 2 10 15 13 2 10 15 14 2 10 15 15 3 16 18 16 3 16 18 17 3 16 18 18 (15 rows affected) -- ```

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

Pawan Khowal

Pawan is a SQL Server Developer. 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 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/

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook: