Leave: Mary asked James to be put out of her misery. James obliges when she comes home from the hospital for a short stay. He packs her into the car and goes to Silent Hill to off himself afterwards. Laura and Mary forgive him because, well... James really was sad and repentant over the whole affair, and they believe he is a good man. Leave implies it was almost entirely a mercy kill. The reasons are all mixed. James definitely drank a lot and he probably at least hit the strip clubs a couple times, but it's what tips the scales over, not the whole story.
Maria: James killed Mary because she was a burden. He really wanted his life back. This is a thing he says in every ending, but Mary either believes him or not. James is happy to take Maria as a replacement and even advises her to do something about her cough, or she knows what follows. This James was certainly affected the most by the lack of sex, but he also probably resented the complete lack of freedom just as much. The fact that he's not even happy about getting a second chance, rather weary that Maria seems to be sick as well is very telling.
In Water: James just straight up died along with Mary when she got her diagnostic. Sure, they both hung on for a few months but James' mind was made up from the start. He did all he could, he tried to support Mary, read up on medicine books, whatever, but the outcome was always gonna be the same. Without the interference of the city, James would have just drove straight for Toluca Lake with Mary's probably still warm corpse. Now this James didn't give a fuck about the lack of sex, it was the lack of Mary that got him. I'm fairly certain the dude would have done ANYTHING to be with the love of his life. I'm pretty sure he would have sold his soul even for another week. This ending and Leave are probably the best because at it's core Silent Hill 2 is a love story, and it's a pretty good love story. This is the James that would do the Rebirth ritual without thinking twice about it.
EDIT: All of Mary's interactions with James are canon, basically, so the ending only changes based on how he deals with it. It's one of the VERY few instances of a multiple ending that works really well because the situation is very well established. Mary asked to be put out of her misery, James perceived her as hateful and a burden, James started going to strip joints and drinking, and he really deeply loved Mary. Whether he's redeemable or not is one of the biggest questions to the player themselves.