General

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

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?

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.