How do I align the pyramid using tsql?-CodePudding

Write an SP that given input an integer n prints in the message window the first n numbers of the Fibonacci sequence, where each number f in the series is defined as follows: f0 = 0 f1 = 1 fn = fn-1

fn-2 (with n>1) For example, the first 10 numbers in the Fibonacci series are: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34

I'm missing the correct statement to align pyramid. Here's the code:

    ALTER PROCEDURE dbo.pyramid(@i int)
    as
    BEGIN
    DECLARE @max INT=4,@n INT=0,@J int =0
    While @i<=@max
    Begin
        WHILE @J<=@i
        BEGIN
            Print space((@max-@j)/2)  REPLICATE((@n @j),(@j))
            Set @j  = 1
        END
    set @i =1
    End
    END
    EXEC dbo.pyramid 1

the result is this:

     1
     22
    333
    4444
the expected result is this one:

       1
      2 2
     3 3 3
    4 4 4 4

CodePudding user response：

I can't fathom the purpose of your procedure, but making a few assumptions does this help at all?

create or alter procedure dbo.pyramid(@i int) as
with n as (select * from (values(1),(2),(3),(4),(5),(6),(7),(8),(9))n(n))

select Concat(Replicate(' ', @i - n), Replicate(Concat(n, ' '), n))
from n
where n <= @i
order by n;

exec dbo.pyramid @i = 9;

        1 
       2 2 
      3 3 3 
     4 4 4 4 
    5 5 5 5 5 
   6 6 6 6 6 6 
  7 7 7 7 7 7 7 
 8 8 8 8 8 8 8 8 
9 9 9 9 9 9 9 9 9

EDIT

Here is a version that will print the pyramid should that be a specific requirement:

create or alter procedure dbo.pyramid(@i int)
as
declare @p varchar(max);
with n as (select * from (values(1),(2),(3),(4),(5),(6),(7),(8),(9))n(n))

select @p = String_Agg(
  Concat(Replicate(' ', @i - n ), Replicate(Concat(n, ' '), n)), Char(13)
) within group (order by n)
from n
where n < = @i;

print @p;

CodePudding user response：

Sure, since the question is about aligning a pyramid, i'd like to propose a fun alternative to @Stu's answer:

CREATE OR ALTER PROCEDURE #pyramid 
(@i int = 9)
AS
BEGIN
IF @i > 9 OR ISNULL(@i,0) < 1
RAISERROR('no',11,1)
ELSE
BEGIN
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;
    ;WITH One2Nine AS (
    SELECT CAST(1 AS int) X
    UNION ALL SELECT X 1 FROM One2Nine o WHERE LEN(o.X 1) < 2
    )
    SELECT 
    --'[' 
    CAST(SUBSTRING(
    REPLICATE(CAST(REPLACE(CAST(CAST(
    ISNULL(NULLIF(ISNULL(STR(NULLIF(X%2,0),1,0),' '),'1'),' ') REPLACE(STR(X,X,0),' ',X)
     AS NCHAR(10)) AS BINARY(20)),0x00,0x20) AS CHAR(20)),2)
    ,23-(9-x),17) AS CHAR(17))
    --  ']'
    FROM
    One2Nine
    WHERE X <= @i ORDER BY X
END
END

EXEC #pyramid 4

This "creates spaces" with switching character encoding and makes use of STR() and ANSI_PADDING of CHAR for the "Alignment"

** EDIT **

This will PRINT :

CREATE OR ALTER PROCEDURE #pyramid 
(@i int = 9)
AS
BEGIN
IF @i > 9 OR ISNULL(@i,0) < 1
RAISERROR('no',11,1)
ELSE
BEGIN

--declare @i int=9
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;
    DECLARE @PrintMsg VARCHAR(200) = '';

    ;WITH One2Nine AS (
    SELECT CAST(1 AS int) X
    UNION ALL SELECT X 1 FROM One2Nine o WHERE LEN(o.X 1) < 2
    )
    SELECT 
    @PrintMsg = COALESCE(@PrintMsg 
    --'[' 
    CAST(SUBSTRING(
    REPLICATE(CAST(REPLACE(CAST(CAST(
    ISNULL(NULLIF(ISNULL(STR(NULLIF(X%2,0),1,0),' '),'1'),' ') REPLACE(STR(X,X,0),' ',X)
     AS NCHAR(10)) AS BINARY(20)),0x00,0x20) AS CHAR(20)),2)
    ,23-(9-x),17) AS CHAR(17))
    --  ']'
      CHAR(13) CHAR(10),'')
    FROM
    One2Nine
    WHERE X <= @i ORDER BY X

    PRINT @PrintMsg
END
END