SQL Puzzle | Fun with SQL-1

In this puzzle you have to print * tree, consider that you have a numbers table.For more details please check the sample input and expected output.

Sample Input

Number
1
2
3
4
5
6
7
8
9
10

Expected Output

Expected Output – 1

Number sname
1 *
2 * *
3 * * *
4 * * * *
5 * * * * *
6 * * * * * *
7 * * * * * * *
8 * * * * * * * *
9 * * * * * * * * *
10 * * * * * * * * * *

Expected Output – 2

Number sname
10 * * * * * * * * * *
9 * * * * * * * * *
8 * * * * * * * *
7 * * * * * * *
6 * * * * * *
5 * * * * *
4 * * * *
3 * * *
2 * *
1 *

Script

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

 ```-- DECLARE @Limit AS INT = 10 ;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 Number FROM Series t1 WHERE Number < (@Limit+1) ORDER By Number GO -- ```

Rules/Restrictions

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

Solution – 1

 ```-- DECLARE @Limit AS INT = 10 ;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 Number, ((SELECT '*'+' ' from Series t2 where t2.Number <= t1.Number ORDER BY Number FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')) sname FROM Series t1 WHERE Number < (@Limit+1) ORDER By Number GO DECLARE @Limit AS INT = 10 ;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 Number, ((SELECT '*'+' ' from Series t2 WHERE t2.Number <= t1.Number ORDER BY Number FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')) sname FROM Series t1 WHERE Number < (@Limit+1) ORDER By Number DESC GO -- ```

Output-1

 ```-- /*------------------------ DECLARE @Limit AS INT = 10 ;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 Number, ((SELECT '*'+' ' from Series t2 where t2.Number <= t1.Number ORDER BY Number FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')) sname FROM Series t1 WHERE Number < (@Limit+1) ORDER By Number GO ------------------------*/ Number sname ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 * 2 * * 3 * * * 4 * * * * 5 * * * * * 6 * * * * * * 7 * * * * * * * 8 * * * * * * * * 9 * * * * * * * * * 10 * * * * * * * * * * (10 row(s) affected) -- ```

Output-2

 ```-- /*------------------------ DECLARE @Limit AS INT = 10 ;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 Number, ((SELECT '*'+' ' from Series t2 WHERE t2.Number <= t1.Number ORDER BY Number FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')) sname FROM Series t1 WHERE Number < (@Limit+1) ORDER By Number DESC GO ------------------------*/ Number sname ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 10 * * * * * * * * * * 9 * * * * * * * * * 8 * * * * * * * * 7 * * * * * * * 6 * * * * * * 5 * * * * * 4 * * * * 3 * * * 2 * * 1 * (10 row(s) affected) -- ```

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com