I came here for this. The ending to Klonoa was shocking as a kid. Hell, you played this cheerful little game and the ending threw a complete curveball at the player. Damn... I feel the chills running up my spine just thinking of it.
As for another suggestion, Popolocrois Monogatari II (and to a lesser extent the localised remake on PSP) had me in tears, particularly the final scene.