Free standing waterfalls.
Rear mounted fountains can be placed along a wall.
Center mounted fountains are better used as a partition wall or in areas visible from both sides. (2 for 1!)
They often come in clear glass which allows the falling water to be seen from either side.

These fountains are fully self-contained and require no additional plumbing.
Water lines or drains may be added but be sure to abide by local permit requirements.