need help for my query to get the number pattern.

Question

Post reply

need help for my query to get the number pattern.

Jay Pete

SSCommitted

Points: 1945
More actions
June 24, 2011 at 1:30 pm

#253387

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

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

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

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

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/

Jay Pete SSCommitted Points: 1945 More actions · Answer 2

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

12345

LutzM SSC Guru Points: 107049 More actions · Answer 3

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]

Jay Pete SSCommitted Points: 1945 More actions · Answer 4

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

LutzM SSC Guru Points: 107049 More actions · Answer 5

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]

Jay Pete SSCommitted Points: 1945 More actions · Answer 6

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Here it is as a WHIE loop

SET NOCOUNT ON

DECLARE @S INT

DECLARE @P VARCHAR(20)

DECLARE @X VARCHAR(20)

SET @X = ' '

SET @S = 1

WHILE (@S < 10)

BEGIN

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

SET @X = @X + @P

SET @S = @S + 1

SELECT @X

PRINT @X

IF @S =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]

Jay Pete SSCommitted Points: 1945 More actions · Answer 8

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

Jay Pete SSCommitted Points: 1945 More actions · Answer 9

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

LutzM SSC Guru Points: 107049 More actions · Answer 10

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]

LutzM SSC Guru Points: 107049 More actions · Answer 11

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]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

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 INT

DECLARE @P VARCHAR(20)

DECLARE @X VARCHAR(20)

SET @X = '123456789'

SET @S = 9

WHILE (@S > 0)

BEGIN

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

SET @S = @S - 1

SELECT @X

PRINT @X

IF @S = 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]

Jay Pete SSCommitted Points: 1945 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 14

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)

need help for my query to get the number pattern.

Cookies on SQLServerCentral