The only time I've really seen this happen in person is when I saw The Lemonheads a couple years ago and they had Nash Kato from Urge Overkill help sing "Girl You'll Be A Woman Soon" for an acoustic portion of their set.
Other than that, I'm going to nominate the unlikely live collaboration between Eels and Afroman for having kind of a funny back-story to it. Eels had stopped playing their Signature Song "Novocaine For The Soul" at the time, and apparently on a prior night on the same tour someone in the audience had angrily confronted E about this after a show... Which inspired them to semi-troll their audience by saying they were going to play their hit, then playing someone else's signature song - they found out Afroman was playing nearby, and "Because I Got High" was all over American radio at the time, so...
edited 4th May '13 4:43:34 PM by MikeK
Heresiarch Command
Dunno where the video is but Kamelot
once got their manager to come onto stage and play a surprisingly good guitar solo with them before leaving.
That moment when musicians are playing, and one of them says they'd like to welcome "A very special guest" to the stage, usually another famous artist they're friends/co-workers with. Done well, this can create some of the absolute coolest moments in music.
List off the ones you think are some of the best.