I think what happened may be a little easier to grasp if you've played the games, but you don't necessarily have to play them first to understand the movie. Here's my take on it:
Contrary to popular belief, Rose and "Sharon" are not dead at the end; they're in a parallel dimension, which is why Chris (the husband) can't see them. To elaborate a bit further...
There are three different dimensions in the movie**:
- The real world: What we see up until the car crash outside of Silent Hill; the dimension that Chris and Officer Gucci are in
- Foggy Silent Hill: A dimension created by Alessa resembling Purgatory; a place of imprisonment for the cultists and a place of refuge from the real world for Alessa
- Dark Silent Hill: A dimension created by Alessa resembling Hell; a place of punishment for the cultists inhabited by monsters; a representation of Alessa's revenge
**Note - Foggy Silent Hill and Dark Silent Hill are not actually Purgatory and Hell. They're only Alessa's versions of them.
Rose isn't able to get back to the real world because Alessa doesn't want them to leave. Both the foggy dimension and the dark dimension were created by Alessa, so she controls who gets in or out. Remember what Dahlia said to Rose near the beginning of the movie: "Only the dark one opens and closes the door to Silent Hill."
Chris and Officer Gucci don't experience what Rose and the others do because they are in two different but parallel dimensions. Chris and Officer Gucci are in the real world and Rose, Cybil, Sharon and the cultists are in Alessa's alternate dimension. That's why they can be in the same place at the same time and not see each other (e.g. - When Rose runs past Chris and he smells her perfume, and in the end when Rose makes it "home" and Chris can't see her).
And as for the ending, here's the deal...
There are three parts of Alessa:
- Alessa (the burned girl/scarred woman in the oxygen tent)
- Sharon (Alessa's good side)
- Dark Alessa (Alessa's evil side)
Sharon and Dark Alessa are basically manifestations of the two sides of Alessa - the good side and the evil side. As for how Alessa created these manifestations and the alternate dimensions (foggy Silent Hill and dark Silent Hill), it can only be assumed that she possessed some kind of supernatural power, as evidenced by the wilted flowers in her hospital room and her hurting the nurse. Alessa's power was awakened by her growing hate as she suffered in the hospital after being nearly burned to death by the cult. That being the case, this is what happened:
1) Alessa's "pact" with her evil half (Dark Alessa) that we saw in the flashback not only ensured that she would help her exact her revenge, but that Alessa, after making herself "whole" again, would also have a second chance at life with a new - and surely in her eyes, better - mother: Rose.
2) Remember that scene during the slaughter of the cultists when Rose tells Sharon to close her eyes and Sharon hesitantly opens them, only to see Dark Alessa staring her in the face? Contrary to what most people think, Dark Alessa did not take over or possess Sharon. It was in that moment that Dark Alessa (Alessa's evil side) reunited with Sharon (Alessa's good side), and became a new and complete reincarnation of Alessa.
3) In the end, Alessa's plan worked - she got her much deserved revenge on the cultists, Dark Alessa and Sharon recombined and created a newly "reborn" Alessa, and she ended up with Rose (who either isn't aware or just doesn't care that this little girl isn't "her Sharon" anymore) as her new mother. Now with Sharon's memories of the outside world to work with after reuniting her two halves, Alessa could extend her alternate dimension and create a world just like the one Sharon and Rose lived in, where they can stay forever. In other words, Alessa doesn't want them to leave her alternate dimension and in her eyes, for good reason. The real world is where Alessa was hurt, abused and humiliated. In the real world, she felt unsafe, unloved and that the people are untrustworthy (especially men, considering that the absence of her father was the source of her torment, in addition to her horrific experience with the school janitor). Alessa wants to live out the rest of her new life with Rose as her new loving mommy in her alternate dimension, where she's in control and she feels safe. Unfortunately for Rose, this means that she is essentially trapped in Alessa's alternate dimension indefinitely.
If you still have a few questions, check out the unofficial FAQ over at IMDb:
http://www.imdb.com/title/tt0384537/board/thread/148368590
Hope this helps. :)