Say I have a table built like this (this is a simplified example):
CREATE TABLE teachers (
course VARCHAR2(12),
teacher VARCHAR2(64)
);
And the contents of the table is this:
INSERT INTO teachers VALUES('1001','Smith');
INSERT INTO teachers VALUES('1002','Smith');
INSERT INTO teachers VALUES('1002','Jones');
INSERT INTO teachers VALUES('1002','Gray');
INSERT INTO teachers VALUES('1002','Davis');
INSERT INTO teachers VALUES('1003','Jones');
INSERT INTO teachers VALUES('1003','Davis');
Assume there will never be more than 4 teachers assigned to a course, how do I query the table to return:
1001,Smith,,,
1002,Smith,Jones,Gray,Davis
1003,Jones,Davis,,
Unfortunately this is for a CSV export to a 3rd party system that we do not control, so we have to send the data this way (as columns padded with blanks as needed). I've looked at PIVOT, but it looks like it can't do dynamic values in plain SQL. Is this possible with SQL or are we going to have to bite the bullet and use code to build the needed output?