Let's say I have a table with with one column made up of 4 rows.
Names
name1
name2
name3
name4
How could I get all permutations of this column rows. ie
name1 name2 name3 name4
name1 name2 name4 name3
ETC.
A:
If something like SQL Server 2005 (and above) you can use the PIVOT command.
kevchadders
2010-09-29 15:24:14
postgresql..........
2010-09-29 15:29:58
+1
A:
join it to itself?
select t1.name, t2.name, t3.name, t4.name
from table t1, table t2, table t3, table t4
Beth
2010-09-29 15:36:17
The thing with this is that you also get lines such as name1, name1, name1, name1. Everything in one row has to be distinct
2010-09-29 15:51:50
OK, I didn't get that from your question. @Bill Karwin's solution should help.
Beth
2010-09-29 16:48:03
+1
A:
select t1.name, t2.name, t3.name, t4.name
from mytable t1
join mytable t2 on t2.name not in (t1.name)
join mytable t3 on t3.name not in (t1.name, t2.name)
join mytable t4 on t4.name not in (t1.name, t2.name, t3.name)
Bill Karwin
2010-09-29 16:15:09