CODEWITHSUNDEEP
sqlsql-server
jimbo1022
jimbo1022

Reputation: 39

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

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.

Upvotes: 3

Views: 251

Answers (3)

Dmitry Polomoshnov
Dmitry Polomoshnov

Reputation: 5374

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.

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

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

Upvotes: 1

mroach
mroach

Reputation: 2478

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

Upvotes: -1

Related Questions