How to draw a triangle in SQL Server?

Question

How to draw triangles in SQL Server as shown below?

I want to implement it by using WHILE loops, but I am unable to print 20 '*' in a single line in SQL Server.

How can I achieve this?

Answer 1

Try This,

DECLARE @StrLen INT = 20

WHILE @StrLen >= 1
BEGIN
    PRINT REPLICATE('*',@StrLen)
    SET @StrLen = @StrLen - 1
END

Answer 2

Try the following code:

Declare @i int,@a int
Set @a = 7
Set @i = -@a;
While (@i<10)
Begin
	Set @i = @i + 1
	Print replicate('* ',@a - abs(@i))
End

You can see more different triangle examples in here

Answer 3

You can use REPLICATE to repeat a character a certain number of times. To generate a sequence of numbers from 1 to 20 you don't need a WHILE anyway - SQL doesn't really need the WHILE statement to work with data.

Number sequences are always useful which is why almost every SQL developer creates a Numbers table.

If you don't already have one, a quick and dirty way to generate 20 numbers is to select the top 20 rows from a systems table, and use ROW_NUMBER to calculate row numbers eg:

select top 20 replicate('*',21-row_number() over (order by id) )
from sys.sysobjects

With a Numbers table, the query is simpler:

select replicate('*',Number )
from dbo.Numbers
where Numbers.Number <= 20
order by Number desc

Numbers tables are extremely useful, eg for sets of elements like 200 days starting from 2017/1/1 :

select dateadd(d,Number,cast('20170101' as date))
from dbo.Numbers
where Numbers.n<= 20
order by Number desc

Answer 4

Instead of a table, values(),(),.. can be used.

Cross join of

(values(5), (4), (3), (2), (1)) t1(x)

and

(values(15), (10), (5), (0)) t2(y)

will result in

(x,y) => (5,15), (4,15), (3, 15), (2, 15), (1, 15)
         (5,10), (4,10), (3, 10), (2, 10), (1, 10)
         ...
         (5,0),  (4,0),  (3, 0),  (2, 0),  (1, 0)

Taking this into account, a query like below can be built:

select replicate('* ', x+y)
from (values(5), (4), (3), (2), (1)) t1(x)
    cross join (values(15), (10), (5), (0)) t2(y)

Answer 5

Use REPLICATE inside a WHILE. I think, you can achieve your desired output if you do it correctly?

DECLARE @i INT = 20

WHILE(@i>0)
BEGIN
   PRINT REPLICATE('* ', @i);
   SET @i = @i - 1;
END