Long ago, I saw a fountain down in Pittsburgh "Strip" district that was basically this. When I started experimenting with fountains, Sharon asked me to make one for her. My apologies to the original artist for not providing proper credits; if it's you, please send me email.
The pump rests in the tub, and sends water up the tube into the 'prinkle can. The tub actually goes down into the neck of the can so that the water comes out of the sprinkle head at pump pressure. It makes a pleasing sprinkley sound. It can run for days on a single tub of water. When we're having guests in the warm months, I'll frequently set it up to run on the porch to amuse people as they come in.