I've had second floor shacks before. First of all, unless the house is very old, chances are the metal cold water pipe doesn't go into the ground at all. It likely is spliced to PVC or such for the underground run. So, you may or may not have the ground you think you do. Second, even if the pipe does go into the ground, it does so many feet from your shack. So, on the higher bands,say 20 thru 10, you may have a quarter wavelength or more of pipe before the ground point. Such a ground isn't a ground at all, but a fairly good antenna. Anyway, make do with what you have. If you determine that the water pipe doesn't go into the ground,have someone install a ground rod for you and run the ground wire up the side of the house and come in through the wall with it. Don't forget- electrical code suggests all your grounds be tied together. So - make sure this ground is connected to the ground for your electrical service. An easy thing to do if it's close to where your shack will be.
There are a lot of easy ways to get antenna feedlines into a second floor shack without new holes in the wall or ceilings. If you have a double hung window (upper and lower that can be raised/lowered), you can simply raise the lower window, run the feedline through, and stuff rags or insulation in the gap (not very elegant!). That comment was for coax. If you're using open wire feedline or a single wire, you can buy a flat strip of insulated wire with a clip at each end and then the window can be closed on that strip with the feedline attached to each end. Another techinque that's useful with a multi-pane window: replace one of the glass panes with a clear acrylic piece. You can then drill holes in it and mount feedthru insulators, or use rubber grommets for coax. You can also use a coax bulkhead feedthru and then screw a coax connector into each end. If you don't mind putting a hole in the side of the house, you can buy electrical wiring components at a home improvment store that you can use to provide a 2 inch or so plastic or metal pipe from the outside to the inside wall. For the outside, you can buy a weatherproof hood that faces downwards. You then stuff each end with fiberglass insulation and you have a weather/wind proof seal. Regarding the ground again, in most cases it serves as an electrical safety measure and does nothing for the antennas.