I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> so if I did combin(35,3) the result would be 6545 and the combinations
>> are
>> listed below. I know how to do it by creating three nested loops in c#
>> but
> I
>> think there's a way to do it in sql by using a cross join or self join
>> but
> I
>> don't see it.
>> Thanks
>>
>> 1 2 3
>> 1 2 4
>> 1 2 5
>> 1 2 6
>> 1 2 7
>> 1 2 8
>> 1 2 9
>> 1 2 10
>> 1 2 11
>> 1 2 12
>> 1 2 13
>> 1 2 14
>> 1 2 15
>> 1 2 16
>> 1 2 17
>> 1 2 18
>> 1 2 19
>> 1 2 20
>> 1 2 21
>> 1 2 22
>> 1 2 23
>> 1 2 24
>> 1 2 25
>> 1 2 26
>> 1 2 27
>> 1 2 28
>> 1 2 29
>> 1 2 30
>> 1 2 31
>> 1 2 32
>> 1 2 33
>> 1 2 34
>> 1 2 35
>> 1 3 4
>> 1 3 5
>> 1 3 6
>> 1 3 7
>> 1 3 8
>> 1 3 9
>> 1 3 10
>> 1 3 11
>> 1 3 12
>> 1 3 13
>> 1 3 14
>> 1 3 15
>> 1 3 16
>> 1 3 17
>> 1 3 18
>> 1 3 19
>> 1 3 20
>> 1 3 21
>> 1 3 22
>> 1 3 23
>> 1 3 24
>> 1 3 25
>> 1 3 26
>> 1 3 27
>> 1 3 28
>> 1 3 29
>> 1 3 30
>> 1 3 31
>> 1 3 32
>> 1 3 33
>> 1 3 34
>> 1 3 35
>> 1 4 5
>>
>sql