# Return all possible combinations for each group

I have got a table looks like this one here shows the student ID and all classes this student took:

``````+-------------+
| ID | Class  |
+-------------+
| 01 |   A    |
| 01 |   B    |
| 01 |   C    |
| 02 |   A    |
| 02 |   C    |
| 02 |   D    |
| 03 |   A    |
| 03 |   B    |
+-------------+
``````

And I would like to convert this table to the one below in order to see all possible combinations of classes for each student.

``````+----+---------+---------+
| ID | Class 1 | Class 2 |
+----+---------+---------+
| 01 | A       | B       |
| 01 | A       | C       |
| 01 | B       | C       |
| 02 | A       | C       |
| 02 | A       | D       |
| 02 | C       | D       |
| 03 | A       | B       |
+----+---------+---------+
``````

Does anyone know how to get this?

-
I have tried to use pivot table, but that would only return me 3 unique row for each ID, and Class 1, Class 2, Class 3 as three different columns. – Vanni Zhang Sep 3 '13 at 22:06
Is the order of the classes important? I see you listed A, B as a possible combination, but not B, A. – Jon Seigel Sep 3 '13 at 22:10
The order is not important at all, that means A, B and B, A are the same. – Vanni Zhang Sep 3 '13 at 22:19

if your dataset is small, something like this could be suitable:

``````select class1.id ,class1.class class1,class2.class class2 from
t1 class1,
t1 class2
where
class1.id = class2.id and
class1.class < class2.class
order by id,class1,class2
``````
-
Thanks for the answer, but my dataset is somehow bigger than this which has more than 1000 IDs. So I need a more dynamic way for this. – Vanni Zhang Sep 3 '13 at 22:22
1000 IDs do not make that big of a table. And even with a really large table, I don't think there is any more efficient way to write this query. – ypercubeᵀᴹ Sep 3 '13 at 22:24
@vanni-zhang I don't understand what you mean with "a more dynamic way" in this context. Do you need more then 3 columns ? – r.m Sep 3 '13 at 22:28
Sorry r.m, I am quite new to database so I was confused. I tried the way you provided and it solved my issue. Thanks a lot! – Vanni Zhang Sep 3 '13 at 22:33
ok, no worries! glad I could help :-) – r.m Sep 3 '13 at 22:35