My favorite is probably the "Game" Plot Twist of Star Ocean: Till the End of Time. At first, I hated it, as it ruined much of the things that happened in earlier Star Ocean games, and Valkyrie Profile. But I started to realize the philisophical meanings and such behind it, and despite the boring character outros of the ending, the ending did make a lot of sense, and was a pretty great ending.
I also loved the stuff that happened at the end of Star Ocean: The Second Story. No, it wasn't really much of a plot twist as it was just a bunch of things thrown together, but it was still awesome. Ronixis being killed, Claude's decision to stay on Expel or leave with his father, and Expel itself being blown to shreds. All in a matter of minutes. It was pretty sweet, if you ask me.
Favorite: FF IV where Kain turned on the team in one of the caves (forgotten which one.) At the time, treachery wasn't that common in any of the rpgs that I had played, and Kain was such a pivotal part of the team that it was so unexpected. A great twist.
Less favorite: SO: TtEoT where the cast learned they were part of some experiment inside of a sphere. Up until that point, the story was somewhat interesting and I liked the hostile invader angle, but once it became computer viruses and whatnot, the story fell apart for me... A surprising and unexpected twist, but really hollow IMO.
**Final Fantasy VII SPOILER**
When Aeris dies, I had no idea that it would happen.... Shocked the hell out of me, and this was my first RPG I ever played! I never realized how involving RPGs were until I played FFVII, until I found myself on my knees crying for a videogame character.... I was like, "Wow!"
Let's see what I can come up with about plot twists.
Strange Good: Star Ocean TTEoT, very odd twist to say the least but I liked it
Strange Bad: Baten Kaitos, goodness there was one odd and crazy twist in that game involving Xelha and what she actually was, but I do give the game credit for originality
Good: As much as I did not enjoy Magna Carta, the story was interesting and the overall plot twists were very good
I have more but as usual a blank is being drawn. lol