How do I produce permutations in the same column using SQL?

Question

I have a table that looks like the following:

LETTERS
--------    
A
B
C
D
E
F
G
H

I'd like to create a View that lists all the 3 letter combinations of these letters without repetition in the following way i.e. assigning a number to each combination.

ViewNew
-------
1 A
1 B
1 C
2 A
2 B
2 D
3 A
3 B
3 E

and so on.

Is the above possible? Any help will be much appreciated.

Answer 1

Check This. Using Joins and UNPIVOT we can find all permutions of letters.

        select ID,ViewNew from 
        (
            select row_number() over(order by (select 1)) AS ID, 
            C2.LETTERS as '1' ,C1.LETTERS AS '2' ,c3.LETTERS as '3' from #tableName C1,#tableName c2,#tableName c3
            where C1.LETTERS != c2.LETTERS  and c2.LETTERS ! = c3.LETTERS and   c1.LETTERS ! = c3.LETTERS
        ) a  
        UNPIVOT
        (
          ViewNew
               FOR [LETTERS] IN ([1], [2], [3])
        )as f

OutPut :