I was musing in the Backlaugust thread that Conker's Bad Fur Day has both very good single-player and multiplayer modes, and it got me wondering what else might fit in this category. Some others that come to mind:
Perfect Dark: a refined version of GoldenEye's single-player with tons of challenge and cheats, while the multi is so extensive I still can't think of a FPS that outdoes it with options and challenges. Rare knows how to cover all their bases.
Streets of Rage 4: Tons of difficulty options and rankings to shoot for in single, with an excellent endless mode for multi.
Pikmin 3: The challenge modes are great fun with a buddy and like all good ranking systems, keep medals separate between both single and multi-player.
Advance Wars (series): Dual Strike in particular comes to mind, but all of these excel greatly in multiplayer due to how tightly balanced the games are. Also, edit modes!
Because of the old cliché "Jack of all trades, master of none" I'm okay with games that focus on either single player or multiplayer to provide the best experiences they can in that one area.
But to answer the question, I was gonna rattle off some of the co-op games as Secret_Tunnel mentioned (I'll toss NSMBWii and U into that pot!), though, are you asking for games which have distinctly different modes for single player and multiplayer? There are probably less of those around, especially these days. Gonna have to agree that Perfect Dark" is the epitome of that.
If we can count DLC then I'd suggest Splatoon 2, since the Octo Expansion was pretty good single player content. The regular single player doesn't have much going for it though.
I was going to suggest Halo and Call of Duty, but I know the audience here, so... I'm just going to back away slowly...
Man, you guys hit a bunch of the good ones. Splatoon (and Halo) are pretty robust, but not equally good in both modes.
Is anything in this world equal?
Monster Hunter definitely plays differently when you need to dodge every attack yourself, but it's the same basic gameplay... Ditto for Tetris and Puzzle Bobble.
Star Control 2 is supposed to have a great single-player mode, but I've only played multi.
PD, Goldeneye, and TimeSplitters should definitely be in consideration.
Actually, GoldenEye might be the best answer. Just because the modes are more equal. Perfect Dark's multiplayer crushes it's single-player. Conker is a great choice, too. And Diddy Kong Racing. Nobody does equality like Rare!
Ooo, Super Mario 64 DS, perhaps. That package was insanely robust.
Yo, didn't Picross have online multiplayer on the DS? Fire Emblem, too. Nintendo really went kind of crazy with multiplayer in that era. Lots of seldom-played goodness.
I’d put in an argument for Pokémon. One might assume the single player crushes the multi, but high-level play against pros can be very intense. And maybe Violet/Scarlet will introduce some great simultaneous co-op as well.