How to make a water foutain effect like this?
I want the water to flow.
If faking it with textures is not an option, then marching cubes is pretty much the only choice (example, code in the bottom right.)
not what I want, is there any particle system that can do this?
This is also not what you want, but sometimes making a specific effect by yourself is faster than looking for already existing effect.
Meanwhile, you can search the forum, there were discussions about water fountains before. Maybe some of the posts there is what you need?
In any case, be prepared that the complexity of the effect raises exponentially with how photorealistic you want it.