How do I transpose multiple rows to columns in SQL

Question

My first time reading a question on here.

I am working at a university and I have a table of student IDs and their supervisors, some of the students have one supervisor and some have two or three depending on their subject.

The table looks like 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 to create a view that turns that into 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 at PIVOT functions, but don't think it matches my needs.

Any help is greatly appreciated.

What happens when a student gets a 4th supervisor? What happens when they get a 937th supervisor? — gvee, Apr 01 '15 at 15:23
I think you are looking for somthing like this. http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Biff MaGriff, Apr 01 '15 at 15:25
HI Gvee, there can be more than a dozen supervisors, but never more than 3 live supervisors, the rest all have end dates so get filtered out. — Mark Dyson, Apr 01 '15 at 17:35

TT. · Answer 1 · 2015-04-01T18:15:12.957

PIVOT was the right clue, it only needs a little 'extra' :)

DECLARE @tt TABLE (ID INT,Supervisor VARCHAR(128));
INSERT INTO @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
    *
FROM
    (
        SELECT
            ID,
            'Supervisor ' + CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Supervisor) AS VARCHAR(128)) AS supervisor_id,
            Supervisor
        FROM
            @tt
    ) AS tt
    PIVOT(
        MAX(Supervisor) FOR
        supervisor_id IN ([Supervisor 1],[Supervisor 2],[Supervisor 3])
    ) AS 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 will notice that the assignment to Supervisor X is done by ordering by the Supervisor-VARCHAR. If you want the ordering done differently, you might want to include an [Ordering] column; then change to ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Ordering]). Eg an [Ordering] column could be an INT IDENTITY(1,1). I'll leave that as an excercise to you if that's what's really needed.

How do I transpose multiple rows to columns in SQL

1 Answers1