Get every permutation of two columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Get every permutation of two columns?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2004-07-20 : 19:29:01

I need to get a table that has every permutation of items that appear in either column A or B of another table. That's not sounding clear, so here's sample DDL/DML:


create table #tSource (item_id1 int,item_id2 int,random_unused int)

insert into #tSource(item_id1,item_id2) VALUES (1,2,55)
insert into #tSource(item_id1,item_id2) VALUES (2,5,66)
insert into #tSource(item_id1,item_id2) VALUES (3,1,77)
insert into #tSource(item_id1,item_id2) VALUES (4,9,88)

create table #tPermutations (item_id1 int, item_id2 int)
insert into #tPermutations (item_id1,item_id2)
     select (your magic here)

In the above example, the result I'd be looking for is:


item_id1  item_id2
--------  --------
1         2
1         3
1         4
1         5
1         9
2         1
2         3
2         4
2         5 
2         9
3         1
3         2
3         4
3         5
3         9
4         1
4         2
4         3
4         5
4         9
5         1
5         2
5         3
5         4
5         9
9         1
9         2
9         3
9         4
9         5

That is, every item that appears in either item_id1 or item_id2 of #tSource will have every permutation of itself and every other item that appears in item_id1 or item_id2 of #tSource. There are other columns, represented here by random_unused, which should be ignored.

I'm stumped. I see how to do it with a bunch of intermediate steps and select distincts, but it seems like there's got to be a better way.

Cheers
-b

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-20 : 19:55:21

sounds like a job for DOCTOR CROSS JOIN:


select a.item_id1, b.item_id2
from 
  (select distinct item_id1 from #tSource) a
cross join
  (select distinct item_id2 from #tSource) b

that's what a cross join calculates: the cartesian product of two sets, or all possible combinations. It can come in very handy!

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-20 : 19:59:39

And be VERY dangerous. :) Gotta love it though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-20 : 21:09:11

Aiken,

insert into #tSource(item_id1,item_id2) VALUES (1,2,55)
Above sql is giving me error in SQL server 7.0 as you are passing 3 values(1,2,55) and specifeid 2 parameters(item_id1,item_id2). Is it a typing mistake?
Cheers
Manoj

aiken
Aged Yak Warrior

525 Posts

Posted - 2004-07-21 : 18:35:32

Sorry, that was a typing mistake. All of those should read (item_id1,item_id2,random_unused)

Thanks for the subselect / group by solution; it works perfectly. And yep, it is dangerous, since it can lead to astronomical numbers of rows. But hey, that's what I need.

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources