Far and away one of the most interesting LRRH-themed games I've heard about is Tale of Tales' The Path, due for release next year. From the official website:
The Path is a short horror game with a unique form of gameplay, designed to immerse you deeply into the dark themes of its story. Every interaction in the game expresses an aspect of the narrative.
There is one rule in the game. And it needs to be broken.
There is one goal. And when you attain it, you die.
Obviously I can't speak on the quality of the finished product, but based on the concept alone this seems like a fascinating game: a perfect example of form mirroring content. As the title illustrates, the game focuses primarily on what path you take through the forest and what choices you, as Little Red Riding Hood(s) make whilst inside. Video games have a complicated relationship with free will and determinism: the player has the illusion of choice, but is really very constricted by both the limiting path mapped out by the game and by the limited grammar of the game's construction, preventing one from truly doing whatever one wants.
The Path exploits this idea, relating it back to the way in which fairy tales such as Little Red Riding Hood operate. The game not only reflects events within the classic tale - Red's choice of the path of needles or the path of pins, the way her mother advised or the way the wolf dares her to travel - but also highlights the meta-narrative of the tale, the fact that the Little Red Riding Hood story itself is one that can go through countless differing iterations (multiple paths) but ultimately must follow the same basic structure and come to the same (in the game, fatal) conclusion.
The game further illustrates this theme of the similar-yet-different stories through the multiple takes on Little Red Riding Hood herself. The game does not limit itself by having one protagonist - just as there is not one Little Red Riding Hood - but instead features multiple lead characters who seem to be reflections on one another and also representative of the various different ways of presenting individual versions of the tale, from Robin, the kid-friendly one, to Ruby, the more American McGee-influenced one, to the adult Scarlet, no doubt representing versions of the tale that seem to explore a more adult sensibility.
Even the structure of the game seems based around the notion of retelling; a key feature of any notable fairy tale. The same story is told over and over again and yet never told twice.
What's also interesting is of course the way that this game focuses on the importance of audience interaction. Video games are naturally a medium entirely based around the concept of the player becoming an active participant in a story crafted by someone else, but the same can also be said of the way fairytales are told, either with young children becoming active participants in the storytelling (chiming along with repitions of "what big eyes you have," etc.) or with the fact that tale-hearers ultimately become tale-tellers, and interact with and construct variations on the story themselves. In the game, the player not only constructs their own version of the story (again, within set limits), but their actions also manipulate such things as the ambient music, "mixed in real time through in-game activity."
Really though, I could go on about this game for ages. At first glance you may just brush it off as some kind of Fable clone, but that would be doing this game a great disservice. The Path doesn't just feature differing paths towards the same end, but uses those paths and the nature of the game's own construction to make a point about the very essence of fairy tales and oral folk tales (and video games) and the way the same stories can repeat over and over again, throughout various stages in our lives (represented by the differing ages of the Red Riding Hood characters) without ever becoming truly tired or repetitive. And wow, do I love it when form is married together with content.
I will very much be anticipating this game's release, set for Spring 2009 for Windows. Look forward to an update some time after that.