The early or even staged Eight Days PS3 gameplay trailer hyped me to no end. Was really looking forward to this game and was devestated when it was cancelled. Yeah, we got Uncharted instead which filled the void of a third person cover adventure shooter, but I just loved the setting of Eight Days. Maybe someday Sony will revive it.
Scalebound as well. I was at gamescom when MS did promo for the game. I attended the Xbox fan fest, got a poster of the game and a shirt. To top it off, I got to meet Kamiya on a freaking boat later that day and took a picture with him while wearing the Scalebound shirt. Also got my poster autographed. I was looking forward to the actual game, but the whole story around it, meeting Kamiya like 1 year before it was cancelled, made the announcement all the worse.
Silent Hills is a good shout, too. I'm not the biggest horror fan, but the SH trilogy is great. Also P.T. was a very special experience, following the communities progress right after it was released. It's just a gigantic "what if" game.
Also Final Fantasy Versus XIII. The released game is not the same that was announced. I would have loved to see what Nomura had in store for us