General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-28-2016
07:37 AM

10-28-2016
07:37 AM

SQL to get all permutations of a set of letters (or numbers)?

I'm trying to create an SQL statement that will take a variable amount of letters and return each possible unique combination of these letters. Simple Example: Given a single column table with 3 rows with the following values....

Index | Element

--------------- 1 | A 2 | B 3 | C

I would need the following permutations returned...

Index | Element

--------------- 1 | A 2 | B 3 | C 4 | A,B

5 | A,C

6 | B,C

7 | A,B,C

Also, for this example, need to get the result set to return with 12 rows that have a type of group code to denote the different permutation elements, such as....

Index | Group# | Element

------------------------ 1 | 1 | A 2 | 2 | B 3 | 3 | C 4 | 4 | A

5 | 4 | B

6 | 5 | A

7 | 5 | C

8 | 6 | B

9 | 6 | C

10 | 7 | A

11 | 7 | B

12 | 7 | C

I've tried a number of cartesian joins and am having no luck. I've found I can do it with three separate queries unioned together, but that doesn't allow for a variable number of inputs (such as all permutations for 6 different letters).

Thanks for any help offerred.

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-01-2016
03:58 AM

11-01-2016
03:58 AM

Re: SQL to get all permutations of a set of letters (or numbers)?

I don't think there's a simple solution for a variable number of input rows, you need either **n-1** Cross Joins or **n-1** Unions. Of course you could use dynamically created SQL in a Stored Procedure to hide this complexity.

Btw, why do you need it?