I have a SQL query that results in a list of students and their advisors. A student can have multiple advisors, but right now there is only one student and one advisor per line, like so:
+---------+---------+
| student | advisor |
+---------+---------+
| 1 | a |
| 2 | x |
| 2 | y |
| 3 | a |
| 4 | b |
+---------+---------+
I would like all advisors for a given student to be on one line:
+---------+----------+----------+----------+
| student | advisor1 | advisor2 | advisor3 |
+---------+----------+----------+----------+
| 1 | a | | |
| 2 | x | y | |
| 3 | a | | |
| 4 | b | | |
+---------+----------+----------+----------+
The number of advisor columns is arbitrary at the moment.
SQL Server dynamic PIVOT query? and Write advanced SQL Select are similar questions, but I don't want the advisors' names themselves to be column headings. I'm at a loss as to how to write the PIVOT statement.
My thought process is to loop through each distinct student, then loop through each of that student's advisors and fill in the columns one by one. However, I'm a SQL beginner and I'm not sure how to tackle this.
