Some video cards have Composite and S-Video in, generally cards with "TV" and video capture features. In my experience the picture quality for video games is poor, though YMMV.
The next option is a cheap (no more than 50 bucks) VGA converter made specifically for game systems. You can increasingly find these in Radio Shack and such. Traditionally the picture quality from these has been weak, since Composite and S-Video just don't look great when piped to a nice, clear VGA monitor. I've heard there's at least one that accepts Component input now, which sounds interesting.
The final, most expensive and highest-quality option is to get a VGA upscan converter called the XRGB-2. It's a Japanese device that can be obtained from a few importers such as NCSX.com. This device can take pure RGB or Component as input, so the PQ is very good. I have one and it's been a lifesafer since I've been between TVs for more than a year. However, it'll set you back at least $200, and probably more.