To answer this question we also need to know why some things are not transparent and why certain things, water for example, don't behave in this way.
A substance's interaction with light is all about interactions between photons and atomic/molecular electrons. Sometimes a photon is absorbed, the absorber lingers a fantasctically short while in an excited state and then a new photon is re-emitted, leaving the absorber in exactly the same state as it was before the process. Thus the absorber's momentum, energy and angular momentum are the same as before, so the new photon has the same energy, same momentum (i.e. same direction) and same angular momentum (i.e. polarisation) as before. This process we call propagation through a dielectric, and, by all the conservations I name, you can easily see that such a material will be transparent.
Sometimes, however, the fleetingly excited absorber couples its excess energy, momentum and so forth to absorbers around it. The photon may feed into molecular (i.e. covalent bond) resonances - linear, rotational and all the other microscopic degrees of mechanical freedom that a bunch of absorbers has. The photon may not get re-emitted, but instead its energy is transferred to the absorbing matter. When this happens, the material is attenuating or opaque.
So, BarsMonster's excellent graph shows us where in the spectrum water's internal mechanics tends to absorb photons for good (thus where it is opaque) and where it behaves as a dielectric, simply delaying the light through absorption and re-emission. In a short answer, it is impossible to explain the whys and wherefores of the graph as its peaks and troughs are owing to molecular resonances of very high complexity. The graph is really as good a simple summary as one is going to get.
However, there is one last piece to the water transparency (in visible light) jigsaw that I don't believe has been talked about and that is water is a liquid. This means it can't be rivven with internal cracks and flaws. Sometimes opaqueness is caused by scattering and aberration rather than the absorption I speak of above. This is why snow is not transparent, for example. For light to propagate through a medium with low enough aberration that we perceive the medium to be transparent, the medium must be optically highly homogeneous. This homogeneity generally arises only in near to perfect crystals and in liquids, the latter tend to smooth out any flaws by flow and diffusion and thus tend to be self homogenising. Inhomogeneity is a powerful block to light: the simplified models of Mie and Rayleigh scattering show this decisively.
So in summary, water is transparent at visible wavelengths because (1) molecular resonances and other mechanical absorbing phenomena don't tend to be excited in water at visible wavelenghts and (2) it is optically homogeneous, which property is greatly helped by its being a liquid.