Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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