Note: I didn't start looking into Linux graphics capabilities in any sort of detailed way until this summer. Prior to that I was usually happy with one monitor working at a standard resolution, using any bland 8-year-old analog video card with a DB15 RGB connector. As a result, any useful experience I might claim to have is probably limited to just 6 months or so. All I can do really is list some thoughts.
You have two categories of video drivers ... open-source drivers, and closed-source drivers. No surprise there.
For ATI cards under Linux you've only got the open-source option. So far as I know, ATI has not yet released a Linux-based driver for any of its graphics cards. (Maybe that's changed?) For NVidia cards you have the choice of either the open-source driver ("nv"), or the closed source driver ("nvidia") that NVidia offers on their website.
For the ATI open-source driver, running "man ati" and "man radeon" indicates that dual head configurations are possible, but there is no rotation support. For the NVidia open-source driver, running "man nv" makes no mention of dual head configurations (other than the fact that you can select one of two outputs), but rotation is supported. However, when you enable the rotation option with the "nv" driver, you switch to an unaccelerated mode.
I can't tell you much about the closed-source "nvidia" driver other than I used it once this summer at work for a unique PCI-based video card. It was hard -- really hard -- to get installed. It did work though, and it provided a lot of options well beyond anything the free "nv" driver had. Dual-head support might have been one of them ... I can't remember.
With my computers I continue to stick with the ATI-or-Nvidia $50-or-less video card options and use the free open-source drivers. My experience with the proprietary Nvidia driver this summer reinforced my personal belief that it's better to go with the open source drivers as packaged by whatever distribution you're using. It's easier to get up and running that way.
Adcadet, have you heard anything about Matrox cards running under Linux? There was a very recent Slashdot discussion about something tangential, and a few people had some very nice words about Matrox and their support for Linux. It was so unusual to hear that sort of support that it stuck in my mind. And now I can't find the Slashdot discussion of course...