I feel that a good illustration of the concept is a comparison between the Rickenbacker 425 and 450 models:
The two-pickup model, like many two-pickup basses, has one pickup very near the bridge and one pickup much further towards the neck; the one-pickup version places that single pickup somewhere in between those two locations.
In my experience, a two-pickup bass gives three distinct tones - a trebly one (bridge only), a fat one (neck only), or a scooped one (both). In contrast, a one-pickup-in-the-sweet-spot yields a tone that is altogether different; it is not as trebly as the bridge location, yet not as boomy as the neck location, but rather seems to have an abundance of crucial mid frequencies. For this reason I tend to conceptualize it as the opposite of the scooped two-pickup tone. Further, a quality two-band preamp seems to be an ideal complement, as it can fill in more low frequencies to approximate a neck pickup, or fill in more high frequencies to approximate a bridge pickup, or yield additional tones that are neither.
Some people espouse the concept of the sweet spot as a mathematical absolute, corresponding to some harmonic antinode or other; this is (as Mushroo pointed out) only applicable if all you play are open strings. The other notion of the sweet spot is much more subjective, and simply means one pickup placed at neither the bridge nor the neck position, but somewhere in between, the location chosen based partly on tone, partly on ergonomics, partly on aesthetics, and partly on tradition (those who emulate the StingRay location, typically).
I like 'em both.
