In Nature, hot water fountains (aka geysers) are effectively steam pumps driven by geothermal energy.
Cold water fountains can be natural or artificial. There are a few that are well-known by road-sides, and have had horse-troughs and so on incorporated into them.
These are basically driven by gravitational energy. A chamber is filled from the bottom by an underground stream. The outlet is at the top, and when it fills, it syphons out the whole chamber by suction. Basically, the chambers turn a continuous flow into a periodic one.
