-1

I have a relatively simple problem but cannot seem to find the solution. This is how my table looks like:

+---------+----------+
| Article | Supplier |
+---------+----------+
|    4711 | A        |
|    4712 | B        |
|    4712 | C        |
|    4712 | D        |
|    4713 | C        |
|    4713 | E        |
+---------+----------+

Now, I want to find all possible 3-way combinations. Each article has to be included in each group (4711, 4712, 4713). For the example above, we will get 6 combination pairs and 18 datasets. The result should look like as follows:

+----------------+---------+----------+
| combination_nr | article | supplier |
+----------------+---------+----------+
|              1 |    4711 | A        |
|              1 |    4712 | B        |
|              1 |    4713 | C        |
|              2 |    4711 | A        |
|              2 |    4712 | B        |
|              2 |    4713 | E        |
|              3 |    4711 | A        |
|              3 |    4712 | C        |
|              3 |    4713 | C        |
|              4 |    4711 | A        |
|              4 |    4712 | D        |
|              4 |    4713 | E        |
|              5 |    4711 | A        |
|              5 |    4712 | D        |
|              5 |    4713 | C        |
|              6 |    4711 | A        |
|              6 |    4712 | D        |
|              6 |    4713 | E        |
+----------------+---------+----------+

I would really appreciate some help.

Tom el Safadi
  • 6,164
  • 5
  • 49
  • 102

1 Answers1

1

I think it is easier to put each combination in a row:

select row_number() over () as combination_nr,
       t1.article, t1.supplier,
       t2.article, t2.supplier,
       t3.article, t3.supplier
from t t1 join
     t t2
     on t2.article > t1.article 
     t t3
     on t3.article > t2.article;

You can unpivot this into separate rows if you really need to.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks a lot for your help! That would work for the example above, however, what I didn't mention is that it could be more than 3 articles. This was just an example with 3 articles. Sorry for not mentioning that. Would you know how to make that work? – Tom el Safadi Dec 10 '20 at 15:11
  • @TomelSafadi . . . This question is *explicitly* about three articles. And this answers the question you asked. If you have a *different* question, then ask a new question. Editing this one just invalidates this answers and encourages downvotes (which is impolite). – Gordon Linoff Dec 10 '20 at 15:13
  • I posted it as a new question. Sorry for not specifying. If you have a solution in mind, I would appreciate your help. Here is the link to the new question: https://stackoverflow.com/questions/65237476/t-sql-return-all-combinations-of-a-table – Tom el Safadi Dec 10 '20 at 15:30