SQL Puzzle: SQL Advance Query – Draw a Triangle without using any Loop

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below two solutions to draw a triangle without using any loop in SQL Server.

Require Output:

Triangle

----------

***

****

*****

Solution 1:

declare @star varchar(10) = '*';

declare @num int = 5;

; with StarCTE as

(

select cast(@star as varchar(10)) as Triangle, 1 as cnt

union all

select cast((Triangle+@star) as varchar(10)) as Triangle, cnt+1 as cnt

from StarCTE

where cnt < @num

)

select Triangle from StarCTE

Solution 2: Fetch number value from the MASTER..SPT_VALUES table of SQL Server.

SELECT REPLICATE(Star,number) Triangle FROM ( SELECT '*' Star ) b

CROSS APPLY

(

SELECT DISTINCT number

FROM

MASTER..SPT_VALUES

WHERE number > 0 and number < 6

) as T

Please try the different solution for this puzzle and share it via comment...

Jun 17, 2017Anvesh Patel

Database Engineer