sql server 2008 r2 - How do I transpose multiple rows to columns in SQL -

my first time reading question on here.

i working @ university , have table of student ids , supervisors, of students have 1 supervisor , have 2 or 3 depending on subject.

the table looks this

id  supervisor 1   john doe 2   peter jones 2   sarah jones 3   peter jones 3   sarah jones 4   stephen davies 4   peter jones 4   sarah jones 5   john doe 

i want create view turns this:

id  supervisor 1    supervisor 2    supervisor 3 1   john doe         2   peter jones     sarah jones  3   peter jones     sarah jones  4   stephen davies  peter jones      sarah jones 5   john doe         

i have looked @ pivot functions, don't think matches needs.

any appreciated.

pivot right clue, needs little 'extra' :)

declare @tt table (id int,supervisor varchar(128)); insert @tt(id,supervisor) values (1,'john doe'), (2,'peter jones'), (2,'sarah jones'), (3,'peter jones'), (3,'sarah jones'), (4,'stephen davies'), (4,'peter jones'), (4,'sarah jones'), (5,'john doe');  select     *     (         select             id,             'supervisor ' + cast(row_number() over(partition id order supervisor) varchar(128)) supervisor_id,             supervisor                     @tt     ) tt     pivot(         max(supervisor)         supervisor_id in ([supervisor 1],[supervisor 2],[supervisor 3])     ) piv; 

result:

id  supervisor 1    supervisor 2    supervisor 3 1   john doe        null            null 2   peter jones     sarah jones     null 3   peter jones     sarah jones     null 4   peter jones     sarah jones     stephen davies 5   john doe        null            null 

you notice assignment supervisor x done ordering supervisor-varchar. if want ordering done differently, might want include [ordering] column; change row_number() over(partition id order [ordering]). eg [ordering] column int identity(1,1). i'll leave excercise if that's what's needed.