Borderlands? Crappy singleplayer? what are you talking about, anyway. . .
I don't see much future for single player games, as companies seem to be realizing, "hey if we pit players against each other then they'll want to win right? And if they want to win than they will take any need necessary to do that right? So we can get them to pay us more money right?" Now I know, some people aren't willing or can't afford to pay for extra content, but at least half of them have already bought the game. This goes primarily for shooters though.
As for MMOs I don't see them actually replacing the standard RPG, after all Dragonage (the first one at least) was a very well made game with a good gameplay and a good story and had nothing to do with online play. On the other hand MMOs do seem to be surpassing traditional RPGs at this point in time, I bet this trend will continue, but we won't see the end of traditional RPGs until we see the end of DnD.
The problem I see for single player games is that AI hasn't been making much progress simply due to the fact that you don't need it, allow me to explain. In CoD the AI is stupid and easy to outmatch, in Timesplitters the AI is simple but challenging, Timesplitters offers Arcade* play with any combination of humans and AI, CoD only allows arcade play with other players primarily via the internet. In defense of the companies, why spend hundreds of dollars making fake people, when you could just connect them with real people? As a result you'll notice that AI hasn't really gotten better than it was in say, Unreal Tournament, since I don't know, Gears of War.
In racing and fighting games I don't see any reason that it won't go in the way of internet only, after all each really has just featured: Cutscene; Arcade Match; Cutscene; Arcade Match. The events being only loosely tied together, and sure each one will always feature its own crappy story mode, but no, that will go the way of the "crappy single player experience."
Rhythm games haven't changed since they came out and won't change ever.
Sports games will go towards internet play only, but I'm not sure anyone cares
Strategy games already rely heavily on online play and that won't decrease in anyway, whether their single player campaigns stick around is anybodies guess (though I say so long as blizzard is around)
So in short: YES I do see singleplayer reaching a point where it is just a weak background for those without internet or that just need a break from multiplayer
*arcade play includes all non-story modes, where people play with the same availabilities (deathmatches, capture the flag, etc.)
