Of course there are a lot of ST fans here, but I haven't seen any discussion on specific episodes. So I'll suggest one of my favourites, from TNG: The Inner Light. This is the one where Picard is zapped by a beam from a probe that makes him live an entire lifetime as a guy named Kamin on a planet that died a thousand years ago. It's a beautiful story, in which Picard gets to experience the family life he's never allowed himself to live, the joys of becoming a father and a grandfather, as well as the pain of losing the woman who shared it all with him. Also, the flute melody Picard learns during that time for some reason brings tears to my eyes, especially when he plays it with the piano accompaniment in a later episode.
Of course, since I'm a huge Barclay fan, I've got to mention another episode: The Nth Degree. I like this one because it gives Barclay a chance to become the kind of man he's always wanted to be, if only for a little while--like the kind of man I wish I could be (just not to that same degree).
Anyway, those are just a couple of my favourites. There are a lot more, from all of the different series (except Enterprise--I never really developed much of an interest in that one).