loop problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

loop problem

Author

Topic

Hotice
Starting Member

9 Posts

Posted - 2006-06-02 : 16:53:49

Problem

I want to use loop to get all the possible combinations of number from 1 to 5 ( or even to n). For example for 1 to 5, I want to get
1
2
3
4
5
1, 2
1, 3
1, 4
1, 5
2, 3
…
1, 2, 3
1, 3, 4
…
1,2, 3, 4, 5

I wrote the following code
declare @counter int
declare @counter1 int
declare @counter2 int

set @counter =0
set @counter1 =0
set @counter2 =0

while @counter<5
begin
set @counter= @counter + 1

while @counter1 < 4
begin
set @counter1 = @counter1+1
while @counter2< 3
set @counter2 =@counter2 + 1
end
print cast(@counter as varchar(20))+ ',' + cast ( @counter1 as varchar(20))+',' + cast ( @counter2 as varchar(20))
end

The result is as following:
1,4,3
2,4,3
3,4,3
4,4,3
5,4,3

But result is not what I want. Is there a way to use loop in SQL server to display all the possible combination? Also if I don’t have a definite end number (for example, it might be from 1 to 5, or it may be from 1 to 10), then how should I code the loop?

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-02 : 17:29:52

Use a cross join, instead of a loop.

Function F_TABLE_NUMBER_RANGE is available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


select
	n1 = a.number,
	n2 = b.number,
	n3 = c.number
from
	dbo.F_TABLE_NUMBER_RANGE(1,5) a
	cross join
	dbo.F_TABLE_NUMBER_RANGE(1,5) b
	cross join
	dbo.F_TABLE_NUMBER_RANGE(1,5) c
order by
	1,2,3

Results:


n1          n2          n3          
----------- ----------- ----------- 
1           1           1
1           1           2
1           1           3
1           1           4
1           1           5
1           2           1
1           2           2
1           2           3
1           2           4
1           2           5
1           3           1
1           3           2
1           3           3
1           3           4
1           3           5
1           4           1
1           4           2
1           4           3
1           4           4
1           4           5
1           5           1
1           5           2
1           5           3
1           5           4
1           5           5
2           1           1
2           1           2
2           1           3
2           1           4
2           1           5
2           2           1
2           2           2
2           2           3
2           2           4
2           2           5
2           3           1
2           3           2
2           3           3
2           3           4
2           3           5
2           4           1
2           4           2
2           4           3
2           4           4
2           4           5
2           5           1
2           5           2
2           5           3
2           5           4
2           5           5
3           1           1
3           1           2
3           1           3
3           1           4
3           1           5
3           2           1
3           2           2
3           2           3
3           2           4
3           2           5
3           3           1
3           3           2
3           3           3
3           3           4
3           3           5
3           4           1
3           4           2
3           4           3
3           4           4
3           4           5
3           5           1
3           5           2
3           5           3
3           5           4
3           5           5
4           1           1
4           1           2
4           1           3
4           1           4
4           1           5
4           2           1
4           2           2
4           2           3
4           2           4
4           2           5
4           3           1
4           3           2
4           3           3
4           3           4
4           3           5
4           4           1
4           4           2
4           4           3
4           4           4
4           4           5
4           5           1
4           5           2
4           5           3
4           5           4
4           5           5
5           1           1
5           1           2
5           1           3
5           1           4
5           1           5
5           2           1
5           2           2
5           2           3
5           2           4
5           2           5
5           3           1
5           3           2
5           3           3
5           3           4
5           3           5
5           4           1
5           4           2
5           4           3
5           4           4
5           4           5
5           5           1
5           5           2
5           5           3
5           5           4
5           5           5

(125 row(s) affected)

CODO ERGO SUM

Hotice
Starting Member

9 Posts

Posted - 2006-06-02 : 18:23:20

Thank you for the replay. The function is cool and fast. But I still have the problem of output format. I will need the output to show like what I showed above(with comma to separate values, ect.). If the number is not too big(such as from 1 to 5), I can manage to output this kind of format. but if the number grows big, then I am not sure how to get the output in the format that I want.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-02 : 22:03:28

simple

select  convert(varchar(10), a.NUMBER) + ',' + convert(varchar(10), b.NUMBER) + ',' + convert(varchar(10), c.NUMBER)
from
	dbo.F_TABLE_NUMBER_RANGE(1,5) a
	cross join
	dbo.F_TABLE_NUMBER_RANGE(1,5) b
	cross join
	dbo.F_TABLE_NUMBER_RANGE(1,5) c
order by
	a.NUMBER, b.NUMBER, c.NUMBER

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-06-03 : 04:30:19

Combinations, not permutations!


SELECT N.NUMBER, SUBSTRING(
  CASE WHEN N.NUMBER & 1 > 0 THEN ', 1' ELSE '' END +
  CASE WHEN N.NUMBER & 2 > 0 THEN ', 2' ELSE '' END +
  CASE WHEN N.NUMBER & 4 > 0 THEN ', 3' ELSE '' END +
  CASE WHEN N.NUMBER & 8 > 0 THEN ', 4' ELSE '' END +
  CASE WHEN N.NUMBER & 16 > 0 THEN ', 5' ELSE '' END,
    3, 8000) AS combination
FROM dbo.F_TABLE_NUMBER_RANGE(1, 31) AS N
ORDER BY 
  SIGN(N.NUMBER & 1) +
  SIGN(N.NUMBER & 2) +
  SIGN(N.NUMBER & 4) +
  SIGN(N.NUMBER & 8) +
  SIGN(N.NUMBER & 16),
  N.NUMBER & 1 DESC,
  N.NUMBER & 2 DESC,
  N.NUMBER & 4 DESC,
  N.NUMBER & 8 DESC,
  N.NUMBER & 16 DESC

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-03 : 05:50:58

You are one smart dude Arnold [emote]Bows to superiority[/emote]

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-06-03 : 07:54:34

SQL Server 2005, using recursive CTEs. Change maxnum as required:


WITH
  MaxNum(maxnum) AS ( SELECT 5 ),
  Pair(n) AS ( SELECT 0 UNION ALL SELECT 1 ),
  Comb(lvl, onbits, n, s) AS (
    SELECT 1, P.n, P.n,
      CAST(CASE WHEN P.n = 1 THEN ', 1' ELSE '' END AS varchar(100))
    FROM Pair AS P
  
    UNION ALL
  
    SELECT C.lvl + 1, C.onbits + P.n, C.n * 2 + P.n,
      CAST(C.s + CASE WHEN P.n = 1 THEN ', ' + CAST(C.lvl+1 AS varchar(100))
        ELSE '' END AS varchar(100)) AS s
    FROM Comb AS C
    INNER JOIN MaxNum AS M ON C.lvl < M.maxnum
    CROSS JOIN Pair AS P
  )
SELECT SUBSTRING(C.s, 3, 8000)
FROM Comb AS C
INNER JOIN MaxNum AS M ON C.lvl = M.maxnum
WHERE C.n > 0
ORDER BY C.onbits, C.n DESC

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-03 : 13:06:46

Oh man .... I hardly have begun to grasp the first one yet ... and now this !!

-- This one's tricky. You need Arnold Fribble to solve this!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources