If you want the flow to be passive, ergo no pumping, then you need a u-bend and a hatch or grate. I recommend the hatch, because the hatch can be used to block flow and prevent water from entering if you need to empty the system.
If you want it below ground, you need a pressure regulator (diagonal passage) on the level where the water will reside.
So:
1. Drop a vertical tunnel/stairwell from the source to the desired depth of the resting water. We'll call this pipe a "vertical main". We'll call the destination Z-level the "plumbing level". Above this level is where wells would go, where fisherdwarves will stand, and so forth. (I use an up/down staircase, in case I need later access, but I've used ramps, cycling hallways with channels, and so forth.) DO NOT CONNECT THIS TUNNEL TO THE SOURCE OF WATER YET.
2. Dig a horizontal water supply tunnel (let's call this a "horizontal main") on the plumbing level that's a few squares long from the vertical main, ending in a ramp to the level ABOVE the plumbing level.
3. Install a grate over the ramp down on the level ABOVE the plumbing level. Grates are invulnerable from below. This prevents critters from getting into the fort through your water mains without preventing water flow, which keeps the system passive.
4. Continue to dig the horizontal main (now one level above the plumbing level) a square or two away from the grate, ending in a ramp back DOWN to the "plumbing level".
5. Place a hatch over the down ramp on the level ABOVE the plumbing level. This hatch is vulnerable to building destroyers already in the main, but they have to get through the invulnerable grate first to reach this spot, or else they're coming from below, and they can't destroy the hatch from below.
6. On the plumbing level, create a "pressure regulator". This is nothing more than a diagonal-to-diagonal connection between two tiles. water entering a regulator can rise no higher than the level the regulator is on, so water AFTER the regulator cannot flood above the "plumbing level".
7. Connect your desired water supplies to the regulated pressure pipe.
Note that if you have many areas in need of water, a single regulated flow may not spread out far enough or fast enough, due to evaporation. In these cases, you may need to leave the water pressurized in all or part of the "plumbing level" and build a separate regulator at/for each destination. Be very careful that you always know which water source/pipes are under pressure, and which are regulated, and to which Z-level they are regulated. Tapping a pressurized pipe is potentially hazardous. NOTE THAT WATER IN ANY SUPPLY PIPE (MAIN) IS ALWAYS "PRESSURIZED".
8. If you want to be able to switch areas on or off the pipes, you need to include a reservoir that can supply each destination, and add floodgates to stop the flow of water from the mains.
9. Place a lever, and attach the hatch to it. Note that when the dwarf adds the mechanism to the hatch in the main, he will be trapped until someone else pulls the lever to open the main. Plan for this.
10. If your system is intended to conduct desalinized water in a salty environment, make sure it is 100% constructed - no natural stone or soil.
11. With your system 100% ready to take on water, conduct a thorough inspection.
a. Are any water destinations connected directly to a main? (This will cause a flood.)
b. Do I have a vertical "u-bend" with access control that will prevent access from outside? (Prevents FBs from swimming into your well.)
c. Do I have a way to shut off the flow? (This allows you to drain the system if you need to do so.)
d. Do I have the means to supply water if the flow is shut off, if I so desire it? (This allows you to supply your well, while the system is drained for expansion/cleaning.)
e. Do I have backup failsafes in case a tantruming dwarf or gremlin throws a lever and tries to drown us all? (This prevents unexpected watery Fun.)
12. Once your inspection passes, connect the vertical main to the source, and let the river passively supply your fortress with clean water. If your hatch is closed, make sure you open it, or the water can't leave the main. Also, if your mechanic is still in the main, you fail at plumbing inspections, you elf.
Note that this system has ZERO complicated mechanics, repeaters, pressure plates, automation, and so forth. It is a 100% dwarf-operated passive supply system. This has advantages (no miscalculations) and drawbacks (requires a dwarf to use levers to change water supply settings). Further improvements in engineering are certainly possible, with planning, and could yield a more automated result, if that's desirable. For instance, one COULD rig a pressure plate to automatically open floodgates or the hatches in the main if supply drops in a particular area. This could allow you to create a system that automatically opens to allow flow, but closes when it's full.
Under normal circumstances, the passive system I've described will never flood, but requires you to either leave it open (safe if you added the grates/hatches as described) or manually handle opening/closing when reservoirs begin to drain due to use. It also assumes you understand that water repressurizes when it drops a level, that you understand the article on pressure in the wiki, and that you don't make an engineering mistake later that sabotages the works.