I'm talking about what happens to your character once you are killed. What games pull the most original stuff that is shown once you die?

You mean like literal death or metaphorical death (i.e: game over but the character is not necessarily killed)?

Most horror games tend to have some gruesome and detailed death animations as a reward for your incompetence. Dead Space, The Evil Within or modern Resident Evil games are an example of it.

Prince of Persia (Sands of Time at least) had that unique piece of dialogue from the Prince whenever you get killed. He was like "ummm, yeah sorry, that's not how it went" or similar pieces because the game itself is narrated through the (living) Prince, so dying was quite contradictory. On the same vein, Fahrenheit/Indigo Prophecy ended the game's narration whenever you triggered a game over, and his narration had actually something to do with how you screw up in those final moments (Like "I was too slow to escape police and I spent the rest of my life in prison").

What happens when your characters health bar or hit count goes to zero.

What if there's no health bar or hit count?

Life

Dead Space. The deaths in those games are brutal

You're just trolling right now aren't you? xD

Then you are just playing Parcheesi.

It's the game over screen though. Damn foot.

I mean, for example, ICO has nothing regarding health or hit bars. Yet it features a somewhat unique animation everytime Yorda gets taken away, or Ico falls to his death.

Likewise, Heart of Darkness, a game about a kid trying to find his lost dog, doesn't feature any kind of health system (everything one-hit-kills you, basically), and it has like a unique death animation for each creature or circumstance that kills you (being drowning the most particular gruesome).