How to get every possible distinct combination?

Asked Jul 27 '21 at 14:52

Active Jul 27 '21 at 14:53

Viewed 116 times

Given the following

CREATE TABLE #Test 
(
ItemName VARCHAR(2)
);

INSERT INTO #Test (ItemName)
VALUES  ('x1'),
    ('x2'),
    ('x3'),
    ('x4');

How can I get every possible combination regardless of the order of the values? For example x1, x2 is the same as x2, x1 so I don't need both. Also, x1, x2, x3 would be the same as x3, x1, x2 or x2, x1, x3

The output would be:

x1, x2
x1, x3
x1, x4
x2, x3
x2, x4
x3, x4
x1, x2, x3
x1, x2, x4
x1, x3, x4
x2, x3, x4
x1, x2, x3, x4

My values would probably go to x16 so I'm trying to keep this simple for the sake of the example.

I've read a bunch of similar questions, but they all stop with combining just 2 values.

Thanks!

edited Jul 27 '21 at 14:53

asked Jul 27 '21 at 14:52

Jeff Brady

1,454
7
35
56

2

Tag your question with the database you are using. – Gordon Linoff Jul 27 '21 at 14:53
2

Use the CTE approach from here : [return-all-possible-combinations-of-values-within-a-single-column-in-sql](https://stackoverflow.com/questions/31070337/return-all-possible-combinations-of-values-within-a-single-column-in-sql) – MatBailie Jul 27 '21 at 15:14
https://stackoverflow.com/questions/3686062/generate-all-combinations-in-sql – Doug Coats Jul 27 '21 at 15:16
The CTE approach worked. Thank you @MatBailie – Jeff Brady Jul 27 '21 at 15:22

How to get every possible distinct combination?

0 Answers0