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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have one master table (teacher) structured like

teacherId   Name        Class
1       Praveen     10
2       John        9

and having a child table (student) structured like

studentId   teacherId   Name
1       1       David
2       1       Creg
3       2       Mike
4       2       Steve
5       2       Jim

How I can get a result set like

teacherId   teacher     studentId_1 student_1   studentId_2 student_2   studentId_3 student_3

1       Praveen     1       David       2       Creg        null        null
2       John        3       Mike        4       Steve       5       Jim

Thanks in advance..

Regards, Praveen

share|improve this question
This is not something along the lines of how SQL is to be used! What would happen, if a teacher had 65536 students for example? You should use a normal query, with a single join, then from your application, aggregate the students under the teachers. The query: SELECT t.teacherId, t.Name, s.studentId, s.Name FROM teacher t JOIN student s ON t.teacherId=student.teacherId – ppeterka Oct 17 '12 at 7:56
Currently we are having 5 to 10 child. Our requirement is to show n number(let us say 3 for now) of row records need to come as columns – Praveen Prasannan Aug 1 '13 at 16:48

Your Answer


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

Browse other questions tagged or ask your own question.