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.
Comments
Post a Comment