You can have story telling elements in both single player and multiplayer, but for single player I find that it is more direct (because you need to pad it with content), rather than indirect / less direct in multiplayer.

For example in both Left 4 Dead games, there is a huge backstory and a lot of secrets and allusions based on wall textures, the plot developing, and replaying it so many times that you've heard every combination of sound clips (I haven't done this)

My brother has over 2000 hours of left 4 dead 2 gameplay. He knows more about it than I do, and I played the game first.

Personally though, I dislike single player games due to a high chance of boredom. I tried to play Metro 2033 and didn't get very far. I played Final Fantasy 9 up until vivi walked onscreen (that's like...5 minutes into the game?). I finished FF8, and I still haven't finished Doom 3 to this very day.

I might not be a single player gamer any more. Maybe when that was all there was, I was one, but now? No.

This isn't always the case though. Starcraft 2 is mostly a multiplayer game but I have finished the campaign along with all of the achievements. I suck at multiplayer so I only play it singleplayer mode (along with its comp stomp achievements and what-not).

I guess where I'm going with this is that it depends, but I prefer multiplayer. Are they dying out? I doubt it. Will it be as successful as the 1990s? Probably never again.