I'm torn between two moments.
Emotional Moment #1: After you beat Sky Keep, and the Isle of the Goddess falls to the surface, Zelda reawakens from her agelong slumber. When Groose first lays eyes on her, he's speechless. This whole game he was not fighting for Link, he was not fighting for Grannie, he was fighting for Zelda. Ever since the beginning of the game he had this 'crush' on Zelda that would never be recognized because Link always got in his way. Anyways, back to the emotional moment, All Groose wanted was a hug from Zelda, but instead Link and Zelda just simply laugh at him. Then, he starts to cry. That seriously peeved me off.
Now, I know this isn't a sad moment, moreso an angry moment, but anger is an emotion too. Why couldn't Zelda have just given poor ol' Groose a hug??? Seriously, it made me fume that Link gets all the credit when he couldn't have even beaten The Imrisoned without the Groosenator!
Emotional Moment #2: After you beat Demise Fi has to part with Link forever and return to the Master Sword. That whole conversation to Link that shows Fi truly cares about him and developed emotions towards him really got to me. The whole game she is characterized by a simple creation with no emotion, and then finally, at the very end of the game, she shows her true colors. It's touching.
Without a doubt, Skyward Sword had the best plotline in the entire series.