need help for my query to get the number pattern.

  • Dear all,

    I am trying to make a triangle pattern using number.

    so please help me ,and my query is below ...

    declare @n1 int

    declare @n2 int

    declare @c1 int

    set @n1 = 1

    set @n2 = 1

    set @c1 = 5

    while(@n1<=@c1)

    begin

    print @n1

    while(@n2<=@c1)

    begin

    set @c1 = @n1 +''+@n2

    set @c1 = @c1 + 1

    end

    print @c1

    set @n1 = @n1 + 1

    set @c1 =0

    end

    Thanks in advance and for your time

  • Not really sure what you are trying to but this will never work as it is. Here is your code formatted for readability.

    declare @n1 int

    declare @n2 int

    declare @c1 int

    set @n1 = 1

    set @n2 = 1

    set @c1 = 5

    while(@n1<=@c1) begin

    print @n1

    while(@n2<=@c1)begin --this will be an endless loop because the value of @n2 or @n3 is never changed inside the loop

    print @c1

    set @c1 = @n1 +''+@n2

    set @c1 = @c1 + 1

    end

    print @c1

    set @n1 = @n1 + 1

    set @c1 =0

    end

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi Sean,

    I am trying to make a triangle pattern like this

    i want to implement while loop.But whenever i try to run then it goes to infinite loop

    my pattern is like this

    1

    12

    123

    123

    12345

  • If you insist in using a while loop: I don't know how to do it. 😉

    But here's a set based solution:

    declare @c1 int

    set @c1 = 5

    ;

    WITH cte_tally AS

    (SELECT Number +1 AS N FROM master..spt_values WHERE type='P' AND Number <@c1)

    SELECT (SELECT n+'' FROM cte_tally t2 WHERE t2.n<=cte_tally.n FOR XML PATH ('') )x

    FROM cte_tally WHERE n<=@c1



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi Lutz,

    Thanks for help me out.

    But it is difficult for me using CTE.

    anyway, i know we can do also another way using nested while loop.

    it's much easier that CTE but i dont getting the same pattern.

    Thanks Lutz

  • jkp2311 (6/24/2011)


    Hi Lutz,

    Thanks for help me out.

    But it is difficult for me using CTE.

    anyway, i know we can do also another way using nested while loop.

    it's much easier that CTE but i dont getting the same pattern.

    Thanks Lutz

    Would you please show me what you expect? Based on your previous post I can't see any difference (ok, there's an additional 4 in my solution in row 4 but I thought that's just a typo on your side...)

    That's the result of the code I proposed:

    1

    12

    123

    1234

    12345



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi Lutz

    You get the right output what i want.

    But i want same output using nested while loop.

    Is that possible?

    If it is let me know?>

    Thanks,

    Jignesh Patel

  • Here it is as a WHIE loop

    SET NOCOUNT ON

    DECLARE @s-2 INT

    DECLARE @P VARCHAR(20)

    DECLARE @X VARCHAR(20)

    SET @X = ' '

    SET @s-2 = 1

    WHILE (@S < 10)

    BEGIN

    SET @P = (CAST(@S AS VARCHAR(10)))

    SET @X = @X + @P

    SET @s-2 = @s-2 + 1

    SELECT @X

    PRINT @X

    IF @s-2 =10

    Break

    ELSE

    CONTINUE

    END

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi bitbucket-25253

    Thanks for Posting your reply.

    It will be helpfull to on millions of way.

    That's the logic what i want.

    I guess it will be helpful all members of our community

    who is the biggeners in T-SQL programming.

    Thanks a lot

    From:

    Jignesh Patel

  • Hi bitbucket-25253

    suppose to be i want make pattern like this :

    12345

    1234

    123

    12

    1

    so i have to use a decrement counter?

    and how can i input the string into the loop?

    let me know..if u know..

    Thanks a lot

    From:

    Jignesh Patel

  • I have to ask again: why do you insist in using a loop?

    Even if there is a set based solution available?

    It seems like performance is not on your list of items being considered when programming. Sad. :crying:

    And no, I don't think the solution Ron (bitbucket) posted is helpful to the community, since it shows the 2nd best option in almost any case. (@Ron: no personal offense intended!)

    Once you're in a situation to deal with a million rows or more or very complex joins, you might remember what I posted...

    If you're a beginner, you should learn it the right way from the very first line of code.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Oh, before I forget:

    To get the result you're looking for (desc order), just add an "ORDER BY cte_tally.n DESC" to the code I posted. 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM

    And no, I don't think the solution Ron (bitbucket) posted is helpful to the community, since it shows the 2nd best option in almost any case. (@Ron: no personal offense intended!)

    No offense taken, in fact your saying 2nd best, is a compliment, when in truth it is the worst possible method. I only posted the code, since I had the gut feeling that the OP was new enough not to understand what a CTE is and how powerful it can be, plus the OP mentioned

    .But whenever i try to run then it goes to infinite loop

    , and thought if I showed the OP how to write a proper WHILE statement some knowledge may have been imparted.

    Now on that note here is another very poor way to do the inverse of the original question. (Again using a WHILE statement.)

    .But whenever i try to run then it goes to infinite loop

    SET NOCOUNT ON

    DECLARE @s-2 INT

    DECLARE @P VARCHAR(20)

    DECLARE @X VARCHAR(20)

    SET @X = '123456789'

    SET @s-2 = 9

    WHILE (@S > 0)

    BEGIN

    SET @X = SUBSTRING(@X,1,@S)

    SET @s-2 = @s-2 - 1

    SELECT @X

    PRINT @X

    IF @s-2 = 0

    Break

    ELSE

    CONTINUE

    END

    Now mr/mrs jkp2311 please, please examine how LutzM's code works, for his solution (using a CTE) is a most powerful tool, not only for solving your immediate request, but for so many other RBAR (Row by agonizing row) problems such as finding duplicate entries in a table.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi Bitbucket

    I find another solution...

    declare @Vint int

    declare @vcount int

    declare @vintnew varchar(200)

    declare @vintnew1 varchar(200)

    set @Vint = 1

    set @vintnew = cast(@vint as varchar(200))

    while @Vint < 5

    begin

    set @Vint = @Vint + 1

    set @vintnew = @vintnew + cast(@vint as varchar(200))

    end

    print @vintnew

    set @vcount = LEN(@vintnew)

    while @vcount > 0

    begin

    set @vintnew1 = substring(@vintnew ,1 ,@vcount-1)

    set @vcount = @vcount -1

    print @vintnew1

    end

    go

  • jkp2311 (6/24/2011)


    Hi bitbucket-25253

    Thanks for Posting your reply.

    It will be helpfull to on millions of way.

    That's the logic what i want.

    I guess it will be helpful all members of our community

    who is the biggeners in T-SQL programming.

    Thanks a lot

    From:

    Jignesh Patel

    Using a loop is absolutely the worst thing to do here. Learning how to use a loop for this is the 2nd worst thing you could do. It's absolutely the wrong thing to learn and there's no way that I'd ever teach a beginner to loop.

    With that in mind, why do you insist on using a loop? It's a two way street here and we'd all like to know why you insist on making this mistake. 😉 What I think is that you were given an assignment by an instructor.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 22 total)

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