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.


Show source
| sql-server   | sql   2017-01-04 06:01 3 Answers

Answers ( 3 )

  1. 2017-01-04 06:01

    If you want to create a list of all unique combinations between two tables, you need only select from both tables at once and SQL Server will give you what you're after. This is called a CROSS JOIN.

    declare @t1 table (letter char(1))
    declare @t2 table (number int)
    
    insert @t1 values ('A'), ('B'), ('C')
    insert @t2 values (1), (2), (3), (4)
    
    select t2.number, t1.letter from @t1 as t1, @t2 as t2
    

    Results

    number  letter
    --------------
    1       A
    1       B
    1       C
    2       A
    2       B
    2       C
    3       A
    3       B
    3       C
    4       A
    4       B
    4       C
    
  2. 2017-01-04 07:01

    For permutations (order is important):

    DECLARE @q as table([No] int, L1 char(1), L2 char(1), L3 char(1))
    
    INSERT INTO @q
    SELECT
        ROW_NUMBER() OVER (ORDER BY L1.Letter, L2.Letter, L3.Letter, L1.Letter), 
        L1.Letter, 
        L2.Letter, 
        L3.Letter
    FROM
        Letters L1 CROSS JOIN
        Letters AS L2 CROSS JOIN
        Letters AS L3
    WHERE
        (L1.Letter <> L2.Letter) AND 
        (L2.Letter <> L3.Letter) AND 
        (L1.Letter <> L3.Letter)
    
    SELECT [No], L1 AS Letter FROM @q
    UNION 
    SELECT [No], L2 FROM @q
    UNION
    SELECT [No], L3 FROM @q
    

    This can actually be done in a single query, yet with repetition of @q query. I would move @q query into subview, if View is the goal.

    Update: Use UNPIVOT to make things even simpler, as pointed out in Bhosale's answer.

  3. 2017-01-04 07:01

    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 :

    enter image description here

◀ Go back