July 11, 2012 at 8:48 am
Racking my brains on this one, does anyone know if it is possible to do a while loop with in a while loop?
what i want to do is loop through the alphabet so the fisrt 27 rows are populated with A1,A2 to A27 then continue loop so once it has got to A27 it then starts at B1 - B27 then C1 and so on.
Here is what i have so far
/* CREATE TABLE #TEST(
ID int identity,
COL1 varchar(10)) */
DECLARE @ID INT,
@COL int
SET @ID=0
SET @COL=64
WHILE @COL<90
BEGIN
SET @COL=@COL+1
while @id<27
Begin
set @id=@id+1
insert into #test
select char(@COL)+cast(@ID as varchar)
end
end
When i Run this SQL the inner while loop works fine however the outer one stops at char 65 (A) can anyone help 🙂
***The first step is always the hardest *******
July 11, 2012 at 8:53 am
Like this
CREATE TABLE #TEST(
ID int identity,
COL1 varchar(10))
DECLARE @ID INT,
@COL int
SET @ID=0
SET @COL=65
WHILE @COL<90
BEGIN
while @id<27
Begin
set @id=@id+1
insert into #test
select char(@COL)+cast(@ID as varchar)
end
set @ID = 0
set @COL = @COL+1
end
July 11, 2012 at 8:55 am
Champion thanks 🙂
***The first step is always the hardest *******
July 11, 2012 at 8:57 am
Not a problem at all sir.
July 11, 2012 at 9:20 am
Looking for something like this? Granted, I hardcoded, but if needed I'm sure I could come up with something more generic.
SELECT
dt.a + CAST(dt1.n AS VARCHAR)
FROM
(VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) dt(a)
CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27)) dt1(n);
July 11, 2012 at 9:24 am
SGT_squeequal (7/11/2012)
Racking my brains on this one, does anyone know if it is possible to do a while loop with in a while loop?what i want to do is loop through the alphabet so the fisrt 27 rows are populated with A1,A2 to A27 then continue loop so once it has got to A27 it then starts at B1 - B27 then C1 and so on.
Here is what i have so far
/* CREATE TABLE #TEST(
ID int identity,
COL1 varchar(10)) */
DECLARE @ID INT,
@COL int
SET @ID=0
SET @COL=64
WHILE @COL<90
BEGIN
SET @COL=@COL+1
while @id<27
Begin
set @id=@id+1
insert into #test
select char(@COL)+cast(@ID as varchar)
end
end
When i Run this SQL the inner while loop works fine however the outer one stops at char 65 (A) can anyone help 🙂
You were almost there!!! You were forgetting to reset your @ID variable. Note my comment in the code below:
DECLARE @test-2 TABLE
(
ID int identity,
COL1 varchar(10)
)
DECLARE @ID INT,
@COL int
SET @ID=0
SET @COL=64
-- Parent loop
WHILE @COL<90
BEGIN
SET @COL=@COL+1
PRINT CHAR(@COL)
--child loop
WHILE @id<27
BEGIN
SET @id=@id+1
print @ID
INSERT INTO @test-2
SELECT CHAR(@COL)+cast(@ID as varchar)
END
-- Missing!!!!
SET@ID = 0
END
SELECT * FROM @test-2
My $0.02: When troubleshooting stuff like this use a temp varable (vs temp table). Once it works you can change your code.
-- Itzik Ben-Gan 2001
July 11, 2012 at 9:28 am
SGT_squeequal (7/11/2012)
Racking my brains on this one, does anyone know if it is possible to do a while loop with in a while loop?what i want to do is loop through the alphabet so the fisrt 27 rows are populated with A1,A2 to A27 then continue loop so once it has got to A27 it then starts at B1 - B27 then C1 and so on.
You can achieve the result even without a While loop by using a Tally table as below
; WITH
-- Tally table Gen Tally Rows: X2 X3
t1 AS (SELECT 1 N UNION ALL SELECT 1 N), -- 4 , 8
t2 AS (SELECT 1 N FROM t1 x, t1 y), -- 16 , 64
t3 AS (SELECT 1 N FROM t2 x, t2 y), -- 256 , 4096
t4 AS (SELECT 1 N FROM t3 x, t3 y), -- 65536 , 16,777,216
t5 AS (SELECT 1 N FROM t4 x, t4 y), -- 4,294,967,296, A lot
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM t3 x, t3 y) -- Change the t3's to one of the other numbers above for more/less rows
SELECTROW_NUMBER() OVER( ORDER BY (SELECT 1) ) ID, CH.N + CAST( Tally.N AS VARCHAR(2)) AS N
FROMTally
CROSS JOIN(
SELECTCHAR(N) AS N
FROMTally
WHEREN BETWEEN 65 AND 90
) CH
WHERETally.N <= 27
I got the code for Tally Table CTE from here
http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/
For more on Tally Table, you can check the below link
http://www.sqlservercentral.com/articles/T-SQL/62867/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 11, 2012 at 9:41 am
Or you could do it this way:
WITH e1(n) AS (SELECT n FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt(n)),
e2(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n FROM e1 a CROSS JOIN e1 b),
charstr(an) AS (SELECT CHAR(64 + a.n) + CAST(b.n AS varchar) FROM (SELECT TOP (26) n FROM e2) a CROSS JOIN (SELECT TOP (27) n FROM e2) b)
SELECT an FROM charstr;
July 11, 2012 at 9:45 am
Or like this:
;WITH Numbers AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27)) d(n))
SELECT CHAR(a.n+64) + CAST(b.n AS VARCHAR(2))
FROM Numbers a CROSS JOIN Numbers b
WHERE a.n < 27
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 11, 2012 at 9:52 am
ChrisM@Work (7/11/2012)
Or like this:
;WITH Numbers AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27)) d(n))SELECT CHAR(a.n+64) + CAST(b.n AS VARCHAR(2))
FROM Numbers a CROSS JOIN Numbers b
WHERE a.n < 27
I like this one, and here is another:
WITH ShortNum(n) AS (SELECT TOP (27) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n FROM sys.all_objects)
SELECT CHAR(a.n + 64) + CAST(b.n AS VARCHAR(2))
FROM ShortNum a CROSS JOIN ShortNum b
WHERE a.n < 27;
Um, seems to be all sorts of ways to generate this particular set of values.
July 11, 2012 at 10:19 am
SGT_squeequal (7/11/2012)
Racking my brains on this one, does anyone know if it is possible to do a while loop with in a while loop?what i want to do is loop through the alphabet so the fisrt 27 rows are populated with A1,A2 to A27 then continue loop so once it has got to A27 it then starts at B1 - B27 then C1 and so on.
Here is what i have so far
/* CREATE TABLE #TEST(
ID int identity,
COL1 varchar(10)) */
DECLARE @ID INT,
@COL int
SET @ID=0
SET @COL=64
WHILE @COL<90
BEGIN
SET @COL=@COL+1
while @id<27
Begin
set @id=@id+1
insert into #test
select char(@COL)+cast(@ID as varchar)
end
end
When i Run this SQL the inner while loop works fine however the outer one stops at char 65 (A) can anyone help 🙂
What do you want to do after you get to "Z27"???
--Jeff Moden
Change is inevitable... Change for the better is not.
July 11, 2012 at 10:47 am
at the moment i dont want to do anything after z27 however, i may then want to re loop it through a second loop to start at AA1 through to ZZ27 :
Thanks everyone for your imput while loop works champion now
***The first step is always the hardest *******
July 11, 2012 at 10:53 pm
SGT_squeequal (7/11/2012)
at the moment i dont want to do anything after z27 however, i may then want to re loop it through a second loop to start at AA1 through to ZZ27 :Thanks everyone for your imput while loop works champion now
Hmmm... Several senior posters have shown you how to do it without a loop. All excellent replies.
The only thing I can add is my mantra:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 12, 2012 at 7:31 am
SGT_squeequal (7/11/2012)
at the moment i dont want to do anything after z27 however, i may then want to re loop it through a second loop to start at AA1 through to ZZ27 :Thanks everyone for your imput while loop works champion now
Do you mean actually "loop" or have you adopted one of the much faster set-based methods?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2012 at 8:05 am
Jeff Moden (7/12/2012)
SGT_squeequal (7/11/2012)
at the moment i dont want to do anything after z27 however, i may then want to re loop it through a second loop to start at AA1 through to ZZ27 :Thanks everyone for your imput while loop works champion now
Do you mean actually "loop" or have you adopted one of the much faster set-based methods?
I counted 5 versions of set-based methods.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply