SQL Puzzle | Repeat the Number Number Times Puzzle

In this puzzle you have to repeat the number number times.

Please check the sample input and the expected output.

Sample Input

NUM
1
2
3

Expected Output

Number
1
2
2
3
3
3

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

 ```-- CREATE TABLE GenerateNumbersByNumber ( NUM INT ) GO INSERT INTO GenerateNumbersByNumber VALUES (1),(2),(3) GO SELECT * FROM GenerateNumbersByNumber 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 * FROM GenerateNumbersByNumber ) SELECT a.NUM Number FROM CTE a INNER JOIN CTE b ON a.NUM >= b.NUM ORDER BY Number -- ```

Output-1

 ```-- Number ----------- 1 2 2 3 3 3 (6 rows affected) -- ```

SOLUTION – 2

 ```-- ;WITH CTE AS ( SELECT * FROM GenerateNumbersByNumber ) SELECT a.NUM Number FROM CTE a CROSS APPLY ( SELECT * FROM CTE b WHERE a.NUM >= b.NUM )u ORDER BY Number -- ```

Output-2

 ```-- Number ----------- 1 2 2 3 3 3 (6 rows 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.

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/

My SQL Groups on Facebook:

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: