SQL - Lets make a Christmas Tree

 The Holiday season is upon us and why not do some fun SQL exercises.

Today we are going to create a Christmas Tree pattern in SQL.

This applies the concept of Recursive CTEs in SQL.

First, lets create a simple pattern using '*'

We can see here that we need to generate an odd numbered pattern of '*'s. And then use some leading space to make the Xmas tree symmetrical.

The odd numbered pattern can be generated as 2*n + 1 for each level.

The space can be (9 - n) for a 10 level tree.

Here is the code to generate this simple tree pattern.

With XmasTree as 

(Select CAST(REPLICATE(' ', 9 ) + '*' as nvarchar(20)) as Level, 1 as n 

UNION ALL 

Select CAST(REPLICATE(' ', 9 - n ) + REPLICATE('*', 2*n + 1) as nvarchar(20)) , n + 1 as n 

FROM XmasTree 

WHERE n < 10)

Select * from XmasTree

Next lets tweak the above code to generate a more attractive pattern -

          *                                                                                         

         *.*                                                                                        

        *...*                                                                                       

       *.....*                                                                                      

      *.......*                                                                                     

     *.........*                                                                                    

    *...........*                                                                                   

   *.............*                                                                                  

  *...............*                                                                                 

 *.................*                                                                                

*...................*                                                                               

        | |                                                                                         

                                                                                          

Here is the SQL code -

With XmasTree as 

(Select CAST(REPLICATE(' ', 10 ) + '*' as nvarchar(25)) as Level, 0 as n 

UNION ALL 

Select CAST(REPLICATE(' ', 9 - n ) + '*' + REPLICATE('.', 2*n + 1) + '*' as nvarchar(25)) , n + 1 as n 

FROM XmasTree 

WHERE n < 10)

Select Level from XmasTree 

UNION ALL

Select CAST(REPLICATE(' ',8) + '| |' as nvarchar(25)) 

And at last a hint that a more visual image can be generated using the spatial data types from SQL.

Here is a simple code below. You can keep adding to the code to add more patterns and shapes to this image.

DECLARE @g TABLE (g GEOMETRY, ID INT IDENTITY(1,1));

INSERT INTO @g(g) VALUES (CAST('POLYGON((0 0,900 0,450 400, 0 0 ))' as geometry).STUnion(CAST('POLYGON((80 330,820 330,450 640,80 330 ))' as geometry)).STUnion(CAST('POLYGON((210 590,690 590,450 800, 210 590 ))' as geometry)));

SELECT g FROM @g ORDER BY ID;