My pet fountain finally died and I didn't want to pay at least $30 for a new one, so I figured out how to do it myself.
Total cost= $16
Since it's my cat who loves the running water, and she spends much of her time on or near my computer desk, I decided to make a table fountain that she and I could both enjoy.
Step 1: Parts
First I found an inexpensive submersible pump at the pet store. It's actually a submersible filter (http://www.aquariumguys.com/duetto.html) and costs $13 at full cost.
Make sure whatever pump you get has a way to modify the water outflow. This pump lets you take off the "Aqua-Sweep" outlet and add tubing. That's crucial to my design.
Then I got a glass bowl from Goodwill, some rocks, and some air line tubing (not the small kind!)
Step 2: Assembly
I then experimented with water levels (if too much of the pump sticks out of the water it won't pump well), rock placement, and flow speed (this pump is adjustable).
To make sure the tubing stayed in the bent shape, I heated it carefully and bent it by hand. I was careful not to do it too much because I didn't want a radical bend, but it seemed to work. I tried a burner on the stove, but found that a lighter a few inches away for short periods worked better.
Then it was just a matter of placing the rocks so the flow of water looked nice and didn't make too much noise.