ALIGNMENT

  • Hello all,

    I have the quest to make Procedure again. 
    But this time I have the syntax, but I dont know how to make it into SP with variable @length, @space, @left/right align ?


    so when I execute :

    1. EXEC USP_ALIGN /*maybe example the name of SP*/ , 5, UP, LEFT
        --RESULT:
    A
    A A
    A A A
    A A A A
    A A A A A

     2. EXEC USP_ALIGN /*maybe example the name of SP*/ , 5, DOWN, RIGHT
         --RESULT:
         A A A A A
           A A A A
             A A A
               A A
                 A

    Please kindly see the attachment image


    DECLARE @cnt INT

    SET @cnt = 1
    WHILE @cnt <= 5
    BEGIN
    PRINT REPLICATE('a ', @cnt)
    SET @cnt += 1
    END
    print ' '
    --DECLARE @cnt INT 
    SET @cnt = 5
    WHILE @cnt >= 0
    BEGIN
    print REPLICATE('A ', @cnt)
    SET @cnt -= 1
    END
    print ' '
    --DECLARE @cnt INT 
    SET @cnt = 1
    WHILE @cnt <= 5
    BEGIN
    print RIGHT(space(20) + REPLICATE('A ', @cnt),20)
    SET @cnt += 1
    END
    print ' '
    --DECLARE @cnt INT 
    SET @cnt = 5
    WHILE @cnt >= 0
    BEGIN
    print RIGHT(space(20) + REPLICATE('A ', @cnt),20)
    SET @cnt -= 1
    END

    If I select all of those syntax it will get the result:


  • Interview question?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster - Wednesday, December 13, 2017 5:04 AM

    Interview question?

    Not an Interview, but it user request said my boss 🙁

  • My next question would be WHY?

    This feels like it is a presentation issue and should be handled in the presentation layer (report or view) and not in the data layer.

    unless you are creating ASCII art then you have probably given us an absctraction of the code output you THINK you are looking for.  Can you give us the actual business problem and there may be a better solution.

  • Try this...
    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_NULLS ON;
    GO
    CREATE PROCEDURE dbo.usp_Align
    /* =================================================================================================================================
    12/13/2017 JL, Created for forum post: https://www.sqlservercentral.com/Forums/1912528/ALIGNMENT
    ================================================================================================================================= */
    /*
    dbo.usp_Align
        @cnt = 25,
        @v_dir = 'D',
        @h_dir = 'R';
    */
        @cnt INT,
        @v_dir CHAR(1),
        @h_dir CHAR(1)
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @_output VARCHAR(8000) = '';

        WITH
            cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
            cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
            cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
            cte_Tally (rep_val) AS (
                SELECT TOP (@cnt)
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM
                    cte_n3 a CROSS JOIN cte_n3 b
                ),
            cte_build_string AS (
                SELECT
                    t.rep_val,
                    pv.pad_val,
                    ov.output_val
                FROM
                    cte_Tally t
                    CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
                    CROSS APPLY ( VALUES (
                                        REPLICATE(CASE WHEN @h_dir = 'L' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'L' THEN t.rep_val ELSE pv.pad_val END)
                                    +    REPLICATE(CASE WHEN @h_dir = 'R' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'R' THEN t.rep_val ELSE pv.pad_val END)
                                        ) ) ov (output_val)
                )
        SELECT
            @_output = (
                        SELECT
                            CONCAT(@_output, '||', bs.output_val)
                        FROM
                            cte_build_string bs
                        ORDER BY
                            CASE WHEN @v_dir = 'U' THEN bs.rep_val ELSE bs.pad_val END
                        FOR XML PATH('')
                        );

        PRINT(REPLACE(@_output, '||', CHAR(13) + CHAR(10)));
    END;
    GO

    Sample output...
    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'L';
    -----------------------------------
    A   
    AA  
    AAA  
    AAAA  
    AAAAA 
    AAAAAA 
    AAAAAAA 
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'L';
    -----------------------------------
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
    AAAAAAA 
    AAAAAA 
    AAAAA 
    AAAA  
    AAA  
    AA  
    A   

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
       A
       AA
       AAA
      AAAA
      AAAAA
      AAAAAA
     AAAAAAA
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
     AAAAAAA
      AAAAAA
      AAAAA
      AAAA
       AAA
       AA
       A

  • aaron.reese - Wednesday, December 13, 2017 6:46 AM

    My next question would be WHY?

    This feels like it is a presentation issue and should be handled in the presentation layer (report or view) and not in the data layer.

    unless you are creating ASCII art then you have probably given us an absctraction of the code output you THINK you are looking for.  Can you give us the actual business problem and there may be a better solution.

    Sounds more like a 'show me that you really can code T-SQL' exercise. 🙂

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • aaron.reese - Wednesday, December 13, 2017 6:46 AM

    My next question would be WHY?

    This feels like it is a presentation issue and should be handled in the presentation layer (report or view) and not in the data layer.

    unless you are creating ASCII art then you have probably given us an absctraction of the code output you THINK you are looking for.  Can you give us the actual business problem and there may be a better solution.

    Jason A. Long - Wednesday, December 13, 2017 1:57 PM

    Try this...
    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_NULLS ON;
    GO
    CREATE PROCEDURE dbo.usp_Align
    /* =================================================================================================================================
    12/13/2017 JL, Created for forum post: https://www.sqlservercentral.com/Forums/1912528/ALIGNMENT
    ================================================================================================================================= */
    /*
    dbo.usp_Align
        @cnt = 25,
        @v_dir = 'D',
        @h_dir = 'R';
    */
        @cnt INT,
        @v_dir CHAR(1),
        @h_dir CHAR(1)
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @_output VARCHAR(8000) = '';

        WITH
            cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
            cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
            cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
            cte_Tally (rep_val) AS (
                SELECT TOP (@cnt)
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM
                    cte_n3 a CROSS JOIN cte_n3 b
                ),
            cte_build_string AS (
                SELECT
                    t.rep_val,
                    pv.pad_val,
                    ov.output_val
                FROM
                    cte_Tally t
                    CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
                    CROSS APPLY ( VALUES (
                                        REPLICATE(CASE WHEN @h_dir = 'L' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'L' THEN t.rep_val ELSE pv.pad_val END)
                                    +    REPLICATE(CASE WHEN @h_dir = 'R' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'R' THEN t.rep_val ELSE pv.pad_val END)
                                        ) ) ov (output_val)
                )
        SELECT
            @_output = (
                        SELECT
                            CONCAT(@_output, '||', bs.output_val)
                        FROM
                            cte_build_string bs
                        ORDER BY
                            CASE WHEN @v_dir = 'U' THEN bs.rep_val ELSE bs.pad_val END
                        FOR XML PATH('')
                        );

        PRINT(REPLACE(@_output, '||', CHAR(13) + CHAR(10)));
    END;
    GO

    Sample output...
    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'L';
    -----------------------------------
    A   
    AA  
    AAA  
    AAAA  
    AAAAA 
    AAAAAA 
    AAAAAAA 
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'L';
    -----------------------------------
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
    AAAAAAA 
    AAAAAA 
    AAAAA 
    AAAA  
    AAA  
    AA  
    A   

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
       A
       AA
       AAA
      AAAA
      AAAAA
      AAAAAA
     AAAAAAA
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
     AAAAAAA
      AAAAAA
      AAAAA
      AAAA
       AAA
       AA
       A

    GilaMonster - Wednesday, December 13, 2017 3:08 PM

    aaron.reese - Wednesday, December 13, 2017 6:46 AM

    My next question would be WHY?

    This feels like it is a presentation issue and should be handled in the presentation layer (report or view) and not in the data layer.

    unless you are creating ASCII art then you have probably given us an absctraction of the code output you THINK you are looking for.  Can you give us the actual business problem and there may be a better solution.

    Sounds more like a 'show me that you really can code T-SQL' exercise. 🙂

    Hai all,

    Im sorry for late Reply,
    Im working as  IT Consultant as SQL Developer. Perivously as DBA.
    And its been 4 months here (placed at Bank) doing New Project,  moving sql files, data, and tasks using ssis.
    sometimes I get bored if there is an error and wait for the programmer's reply for a long time, so I ask to do something to get rid of my boredom. :))@jason A. long --- thank you for the script

    Btw (By The Way) I think I got my answer, with my own and please advice if there is need more syntax or anything..lets discuss..


    Create PROCEDURE USP_ALIGNMENT (@OPT TINYINT, @PIL VARCHAR(5),@JUSTIFY VARCHAR(10))
    AS
    DECLARE @cnt INt

    IF @OPT = 1
    BEGIN
    SET @cnt = 1
    WHILE @cnt <= 5
    BEGIN
    set @pil = 'UP'
    SET @Justify = 'LEFT'
    PRINT REPLICATE('a ', @cnt)
    SET @cnt += 1
    END
    END

    ELSE IF @OPT = 2
    BEGIN
    --DECLARE @cnt INT
    SET @cnt = 5
    WHILE @cnt >= 0
    BEGIN
    set @pil = 'DOWN'
    SET @Justify = 'LEFT'
    print REPLICATE('A ', @cnt)
    SET @cnt -= 1
    END
    END

    ELSE IF @OPT = 3
    BEGIN
    --DECLARE @cnt INT
    SET @cnt = 1
    WHILE @cnt <= 5
    BEGIN
    set @pil = 'UP'
    SET @Justify = 'RIGHT'
    print RIGHT(space(20) + REPLICATE('A ', @cnt),20)
    SET @cnt += 1
    END
    END

    ELSE IF @OPT = 4
    BEGIN
    --DECLARE @cnt INT
    SET @cnt = 10
    WHILE @cnt >= 0
    BEGIN
    set @pil = 'DOWN'
    SET @Justify = 'RIGHT'
    print RIGHT(space(20) + REPLICATE('a ', @cnt),20)
    SET @cnt -= 1
    END

    Thank you very much

  • Jason A. Long - Wednesday, December 13, 2017 1:57 PM

    Try this...
    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_NULLS ON;
    GO
    CREATE PROCEDURE dbo.usp_Align
    /* =================================================================================================================================
    12/13/2017 JL, Created for forum post: https://www.sqlservercentral.com/Forums/1912528/ALIGNMENT
    ================================================================================================================================= */
    /*
    dbo.usp_Align
        @cnt = 25,
        @v_dir = 'D',
        @h_dir = 'R';
    */
        @cnt INT,
        @v_dir CHAR(1),
        @h_dir CHAR(1)
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @_output VARCHAR(8000) = '';

        WITH
            cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
            cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
            cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
            cte_Tally (rep_val) AS (
                SELECT TOP (@cnt)
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM
                    cte_n3 a CROSS JOIN cte_n3 b
                ),
            cte_build_string AS (
                SELECT
                    t.rep_val,
                    pv.pad_val,
                    ov.output_val
                FROM
                    cte_Tally t
                    CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
                    CROSS APPLY ( VALUES (
                                        REPLICATE(CASE WHEN @h_dir = 'L' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'L' THEN t.rep_val ELSE pv.pad_val END)
                                    +    REPLICATE(CASE WHEN @h_dir = 'R' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'R' THEN t.rep_val ELSE pv.pad_val END)
                                        ) ) ov (output_val)
                )
        SELECT
            @_output = (
                        SELECT
                            CONCAT(@_output, '||', bs.output_val)
                        FROM
                            cte_build_string bs
                        ORDER BY
                            CASE WHEN @v_dir = 'U' THEN bs.rep_val ELSE bs.pad_val END
                        FOR XML PATH('')
                        );

        PRINT(REPLACE(@_output, '||', CHAR(13) + CHAR(10)));
    END;
    GO

    Sample output...
    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'L';
    -----------------------------------
    A   
    AA  
    AAA  
    AAAA  
    AAAAA 
    AAAAAA 
    AAAAAAA 
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'L';
    -----------------------------------
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
    AAAAAAA 
    AAAAAA 
    AAAAA 
    AAAA  
    AAA  
    AA  
    A   

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
       A
       AA
       AAA
      AAAA
      AAAAA
      AAAAAA
     AAAAAAA
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
     AAAAAAA
      AAAAAA
      AAAAA
      AAAA
       AAA
       AA
       A

    Hi Jason,
    This sp really work..Cool..thank you

    I do not understand about using 'With' and 'cte'

  • https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thank you GilaMonster

  • unas_sasing - Wednesday, December 13, 2017 8:10 PM

    Jason A. Long - Wednesday, December 13, 2017 1:57 PM

    Try this...
    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_NULLS ON;
    GO
    CREATE PROCEDURE dbo.usp_Align
    /* =================================================================================================================================
    12/13/2017 JL, Created for forum post: https://www.sqlservercentral.com/Forums/1912528/ALIGNMENT
    ================================================================================================================================= */
    /*
    dbo.usp_Align
        @cnt = 25,
        @v_dir = 'D',
        @h_dir = 'R';
    */
        @cnt INT,
        @v_dir CHAR(1),
        @h_dir CHAR(1)
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @_output VARCHAR(8000) = '';

        WITH
            cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
            cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
            cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
            cte_Tally (rep_val) AS (
                SELECT TOP (@cnt)
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM
                    cte_n3 a CROSS JOIN cte_n3 b
                ),
            cte_build_string AS (
                SELECT
                    t.rep_val,
                    pv.pad_val,
                    ov.output_val
                FROM
                    cte_Tally t
                    CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
                    CROSS APPLY ( VALUES (
                                        REPLICATE(CASE WHEN @h_dir = 'L' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'L' THEN t.rep_val ELSE pv.pad_val END)
                                    +    REPLICATE(CASE WHEN @h_dir = 'R' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'R' THEN t.rep_val ELSE pv.pad_val END)
                                        ) ) ov (output_val)
                )
        SELECT
            @_output = (
                        SELECT
                            CONCAT(@_output, '||', bs.output_val)
                        FROM
                            cte_build_string bs
                        ORDER BY
                            CASE WHEN @v_dir = 'U' THEN bs.rep_val ELSE bs.pad_val END
                        FOR XML PATH('')
                        );

        PRINT(REPLACE(@_output, '||', CHAR(13) + CHAR(10)));
    END;
    GO

    Sample output...
    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'L';
    -----------------------------------
    A   
    AA  
    AAA  
    AAAA  
    AAAAA 
    AAAAAA 
    AAAAAAA 
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'L';
    -----------------------------------
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
    AAAAAAA 
    AAAAAA 
    AAAAA 
    AAAA  
    AAA  
    AA  
    A   

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
       A
       AA
       AAA
      AAAA
      AAAAA
      AAAAAA
     AAAAAAA
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
     AAAAAAA
      AAAAAA
      AAAAA
      AAAA
       AAA
       AA
       A

    Hi Jason,
    This sp really work..Cool..thank you

    I do not understand about using 'With' and 'cte'

    I'm glad you like the proc code... As Gail already pointed out the The "WITH CTE" is a common table expression.
    What's more important is the code that in the CTEs... It's what's commonly referred to as a "Ben-Gan Tally Table". Named for Itzik Ben-Gan, the brilliant guy who came up with the idea.
    What makes it special is the fact that it's able to (almost magically) materialize new rows in a set based manner, making it orders of magnitude faster than cursors, while loops & recursive CTEs.

    That said, looking at the code I posted yesterday, there are two flaws (or at least two things I'd change)...
    1) Using a scalar variable to "print" the results, limits the output to 8000 total characters. This would apply even if you were to change the data type to VARCHAR(MAX). Assuming my math is correct, that means my code is limited to a maximum @cnt value of 88 before values start getting truncated.
    2) When the values are "Left" aligned, there should be no reason to "pad" the values to the right... Meaning that it's doing more work and taking up more space  than necessary.

    Both should be fairly easy fixes. If I have time, I'll try to fix both later today.

  • This version should be better for @cnt values over 44 (I also missed the space between the replication values the 1st time).

    SET NOCOUNT ON;

    DECLARE
      @cnt INT = 150,
      @v_dir CHAR(1) = 'D',
      @h_dir CHAR(1) = 'R';

        IF OBJECT_ID('tempdb..#_temp', 'U') IS NOT NULL
        BEGIN DROP TABLE #_temp; END;

      DECLARE
            @_print_row INT = 0,
            @_output VARCHAR(8000) = '';

      WITH
       cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
       cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
       cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
       cte_Tally (rep_val) AS (
        SELECT TOP (@cnt)
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
          cte_n3 a CROSS JOIN cte_n3 b
        )
    SELECT
      t.rep_val,
      pv.pad_val,
      ov.output_val
        INTO #_temp
    FROM
      cte_Tally t
      CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
      CROSS APPLY ( VALUES (CASE WHEN @h_dir = 'R' THEN REPLICATE(' ', pv.pad_val) ELSE '' END + REPLICATE('A ', t.rep_val)
            ) ) ov (output_val);

    WHILE EXISTS (SELECT 1 FROM #_temp t WHERE (@v_dir = 'U' AND t.rep_val > @_print_row) OR (@v_dir = 'D' AND t.pad_val > @_print_row) )
    BEGIN
        SET @_print_row = @_print_row + 1;

        SELECT
            @_output = t.output_val
        FROM
            #_temp t
        WHERE
            (t.rep_val = @_print_row AND @v_dir = 'U') 
            OR
            (t.pad_val = @_print_row AND @v_dir = 'D');

        PRINT(@_output);
    END;

  • Jason A. Long - Thursday, December 14, 2017 7:43 AM

    unas_sasing - Wednesday, December 13, 2017 8:10 PM

    Jason A. Long - Wednesday, December 13, 2017 1:57 PM

    Try this...
    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_NULLS ON;
    GO
    CREATE PROCEDURE dbo.usp_Align
    /* =================================================================================================================================
    12/13/2017 JL, Created for forum post: https://www.sqlservercentral.com/Forums/1912528/ALIGNMENT
    ================================================================================================================================= */
    /*
    dbo.usp_Align
        @cnt = 25,
        @v_dir = 'D',
        @h_dir = 'R';
    */
        @cnt INT,
        @v_dir CHAR(1),
        @h_dir CHAR(1)
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @_output VARCHAR(8000) = '';

        WITH
            cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
            cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
            cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
            cte_Tally (rep_val) AS (
                SELECT TOP (@cnt)
                    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM
                    cte_n3 a CROSS JOIN cte_n3 b
                ),
            cte_build_string AS (
                SELECT
                    t.rep_val,
                    pv.pad_val,
                    ov.output_val
                FROM
                    cte_Tally t
                    CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
                    CROSS APPLY ( VALUES (
                                        REPLICATE(CASE WHEN @h_dir = 'L' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'L' THEN t.rep_val ELSE pv.pad_val END)
                                    +    REPLICATE(CASE WHEN @h_dir = 'R' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'R' THEN t.rep_val ELSE pv.pad_val END)
                                        ) ) ov (output_val)
                )
        SELECT
            @_output = (
                        SELECT
                            CONCAT(@_output, '||', bs.output_val)
                        FROM
                            cte_build_string bs
                        ORDER BY
                            CASE WHEN @v_dir = 'U' THEN bs.rep_val ELSE bs.pad_val END
                        FOR XML PATH('')
                        );

        PRINT(REPLACE(@_output, '||', CHAR(13) + CHAR(10)));
    END;
    GO

    Sample output...
    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'L';
    -----------------------------------
    A   
    AA  
    AAA  
    AAAA  
    AAAAA 
    AAAAAA 
    AAAAAAA 
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'L';
    -----------------------------------
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
    AAAAAAA 
    AAAAAA 
    AAAAA 
    AAAA  
    AAA  
    AA  
    A   

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'U',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
       A
       AA
       AAA
      AAAA
      AAAAA
      AAAAAA
     AAAAAAA
    AAAAAAAA
    AAAAAAAAA
    AAAAAAAAAA

    dbo.usp_Align
        @cnt = 10,
        @v_dir = 'D',
        @h_dir = 'R';
    ---------------------------------- (the raggedness is forum formatting)
    AAAAAAAAAA
    AAAAAAAAA
    AAAAAAAA
     AAAAAAA
      AAAAAA
      AAAAA
      AAAA
       AAA
       AA
       A

    Hi Jason,
    This sp really work..Cool..thank you

    I do not understand about using 'With' and 'cte'

    I'm glad you like the proc code... As Gail already pointed out the The "WITH CTE" is a common table expression.
    What's more important is the code that in the CTEs... It's what's commonly referred to as a "Ben-Gan Tally Table". Named for Itzik Ben-Gan, the brilliant guy who came up with the idea.
    What makes it special is the fact that it's able to (almost magically) materialize new rows in a set based manner, making it orders of magnitude faster than cursors, while loops & recursive CTEs.

    That said, looking at the code I posted yesterday, there are two flaws (or at least two things I'd change)...
    1) Using a scalar variable to "print" the results, limits the output to 8000 total characters. This would apply even if you were to change the data type to VARCHAR(MAX). Assuming my math is correct, that means my code is limited to a maximum @cnt value of 88 before values start getting truncated.
    2) When the values are "Left" aligned, there should be no reason to "pad" the values to the right... Meaning that it's doing more work and taking up more space  than necessary.

    Both should be fairly easy fixes. If I have time, I'll try to fix both later today.

    Jason A. Long - Thursday, December 14, 2017 11:43 AM

    This version should be better for @cnt values over 44 (I also missed the space between the replication values the 1st time).

    SET NOCOUNT ON;

    DECLARE
      @cnt INT = 150,
      @v_dir CHAR(1) = 'D',
      @h_dir CHAR(1) = 'R';

        IF OBJECT_ID('tempdb..#_temp', 'U') IS NOT NULL
        BEGIN DROP TABLE #_temp; END;

      DECLARE
            @_print_row INT = 0,
            @_output VARCHAR(8000) = '';

      WITH
       cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
       cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
       cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
       cte_Tally (rep_val) AS (
        SELECT TOP (@cnt)
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
          cte_n3 a CROSS JOIN cte_n3 b
        )
    SELECT
      t.rep_val,
      pv.pad_val,
      ov.output_val
        INTO #_temp
    FROM
      cte_Tally t
      CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
      CROSS APPLY ( VALUES (CASE WHEN @h_dir = 'R' THEN REPLICATE(' ', pv.pad_val) ELSE '' END + REPLICATE('A ', t.rep_val)
            ) ) ov (output_val);

    WHILE EXISTS (SELECT 1 FROM #_temp t WHERE (@v_dir = 'U' AND t.rep_val > @_print_row) OR (@v_dir = 'D' AND t.pad_val > @_print_row) )
    BEGIN
        SET @_print_row = @_print_row + 1;

        SELECT
            @_output = t.output_val
        FROM
            #_temp t
        WHERE
            (t.rep_val = @_print_row AND @v_dir = 'U') 
            OR
            (t.pad_val = @_print_row AND @v_dir = 'D');

        PRINT(@_output);
    END;

    Thank you for the explanation and the scripts.
    It help me a lot. I will keep learning..

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply