Surely, the most famous such song (if measured by success on the charts) is "Lola" by The Kinks (1970). It tells of a romantic young man's confused encounter with a transvestite, and made the Top 10 in both Britain and the United States.
Here's a link to the music, with full lyrics:
http://www.youtube.com/watch?v=gaMS_5i0Bbs