Generating a set of permutations in SQL Server without reverse duplicates

Question

Table Code:

Col1
----
A1
A2
A3
B1
B2
C1
D1
D2

(I have other columns as well)

I am trying to create every possible combination EXCLUDING itself (i.e COL1:A1 COL2:A1) EXCLUDING havING it again the reverse way (i.e A1 A2, A2,A1)... They are to be in separate columns and there are other columns included as well. I am a newbie, go easy on me :)

So far I have:

SELECT 
    a.Col1, a.[differentcolumn],
    b.Col1, b.[differentcolumn] 
FROM 
    [dbo].code a
CROSS JOIN 
    [dbo].code b
WHERE 
    a.[col1] != b.[col1]

This is almost it but it gives me:

A1    A2
A2    A1

I only want it one way (The first one). How do I do this?

Answer 1

I'm not completely clear on your requirement, but do you just need this?

SELECT 
    a.Col1, a.[differentcolumn],
    b.Col1, b.[differentcolumn] 
FROM 
    [dbo].code a 
       INNER JOIN [dbo].code b ON a.[col1] < b.[col1]

This will join the table to itself on col1, but using < means that you won't see the values where the left-hand copy has a col1 greater than or equal to the right-hand copy, which seems to be what you want.