Sounds like your IDENTITY column has been reseeded at some point. This, like it sounds, resets the seed. So it might have reset back to a prior available number. This...

Had a look at that one earlier,...

From your data, how do you know what courses Student 3 attends? Is there a StudentID column in Courses as well,or does a student attend all courses within a registration?...

Do you have to do this in T-SQL? Pivoting a data in T-SQL can be quite a costly process, and if you require it to be dynamic, even more so...

I've not been able to find anything on this topic (thus no solution sorry). It might be worth raising a connect item, if it is a "feature" then someone might...

Do you need to write VBA for something like this? Would it not be simplier, if this is a one off, to right click the tab, move/copy it to a...

Is the last line always Total? if so then just why note use an a conditional split with the expression:
[ID] <> "Total:"

Assuming that the total value is...

Ahh, now I see what you want.

Try something like this:
WHERE DateColumn >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)
AND DateColumn < CASE...

A better question at this stage is what are you actually trying to achieve? I don't mean the conversion of your values, but where is this information going to be...

Your Boolean expression needs to go in your WHEN clause, not in your THEN (which returns the value).

For example:
CASE WHEN Number BETWEEN 0 AND 9...

Are you expecting to return more than one value back at a time? A simple dataset like this is something to cause you problem if so:

MyType  MyValue
------ ...