In coastal areas and river bottoms, and most places not in hard rock country, water is often produced from sand. Sand is an excellent aquifer because it has the porosity and permeability to produce huge quantities of water. It also has great filtration properties.
Most sand aquifers started their geological lives as boulders or bedrock. Weathering and erosion breaks the massive rocks to particle size, and wind or water flow moves them to where we find them now. They are usually, eventually covered by layers of impervious clays and the like. The amount of time underground, and the depth and weight of the overburden, compacts the sand. An extreme example would be sandstone. This is just plain sand that has been compressed long enough to pretty much solidify. It can be hard enough to be used for building stone!