The best case is a specialist who is desired all the time.
It depends on what you're eating...
FourSquare might have had some really awesome generalists build a really awesome site and architect a really cool platform, but ultimately, when they were completely down to a very complicated problem with MongoDB, they had to bring in a specialist.
I consider myself a generalist, in that I can build all parts of an application on a variety of languages, platforms or technologies, but when something really goes awry, or when I need in-depth knowledge of something that I either don't have time or prerequisite skills to learn, I bring in a specialist.
The pros of being a generalist is that it stays fresh, stays exciting. I can keep pace on something by switching gears. If I get stuck on a design element, I can switch to a programming task. Stuck on a programming task, and I can switch to tuning the infrastructure. Etc., etc. For me, it works well.
Sometimes it's hard to justify what exactly it is that I do though, to people who are used to something more easily pigeonholed. I generally just say I'm a "technologist" to non-techs or family, but if I'm chatting with my peers, it can become harder to explain what an "architect" is, or explain my strengths, weaknesses, etc.
It's often also frustrating when you have a problem in front of you, and you can't find the solution. Sometimes the "If only I knew more deeply" creeps in, or I find the answer on StackOverflow and the fix seems like it would have been really easy if I'd spent more time in language x, or whatever.
My brother is a PostgreSQL guy, and knows it REALLY REALLY well, and that's pretty much all he does. He's pretty much a badass specialist and has a substantial rate to match, but he doesn't necessarily always keep busy either.
To me, I feel like maybe it would get boring, but he knows it inside and out, and is quite obviously "the guy to call" when something isn't right. Any DB is certainly enough to take a good long while to master, and you might never get to know it 100%, but it's nice knowing there are people like him that know it that deeply.
The major con of being a specialist is that your skills are tied to the market for whatever it is you specialize in. If Postgres flops, or is replaced by something else, or the world stops fancying it, he's got problems. Of course, the flip side to that is that he's got time. Even if Oracle goes under (or maybe especially if they go under,) there's still a market for Oracle experts for a good while as there are companies still relying on it that can't switch over quickly.
So, TL;DR, The world needs both, and both can be fulfilling to a given person.
My Dad is a general physician, and hence is a generalist. If you knew how Physicians' function you might know that he is the first person you need to see if and when you have a medical issue. And once he diagnoses the problem he might refer the case to an appropriate specialist.
I've dealt with the bottom falling out very recently. I spent last summer becoming a specialist in Adobe Flex for our desktop app and we know what happened there.