Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
what have you tried so far ? –  Kin Sep 3 '13 at 21:53
    
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
1  
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
add comment

1 Answer 1

up vote 4 down vote accepted

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.