The mirror of Erised shows you the things you want the most in life. Sam saw himself with the woman he loved, living a normal life. Castiel saw himself protecting all innocents and all of gods creation. Dean saw himself with his brother and friend, because even with the literal hell he's been through, even with great power and terrible fates within his grasp, all he wanted was them.
And if you want a sad ending...
He himself isn't in the pictures he sees.