Character Analysis
As the Gryffindor ghost, Nick has always floated around friendlily, speaking to the Gryffindors at their Welcome Feast and generally being a surprisingly positive contribution to their lives (for a ghost). By the end of Book 5, though, as Harry is struggling with the loss of Sirius, Nick's status as a ghost suddenly becomes much more important.
It occurs to Harry to think, if Nearly Headless Nick has stuck around, maybe Sirius is still here, too. Nick clearly looks uncomfortable with Harry's questions. Finally, he has to tell Harry that no, Sirius Black would not have become a ghost. Nick is sure that Sirius has gone on. The only people who decide to linger here on earth as ghosts are people who are afraid of what comes next. Nick tells Harry straight out:
I was afraid of death [...] I chose to remain behind. I sometimes wonder whether I oughtn't to have ... well, that is neither here nor there ... in fact, I am neither here nor there [...] I know nothing of the secrets of death, Harry, for I chose my feeble imitation of life instead. (38.166)
Nick may be dead, but he knows nothing more about the mysteries of what happens next than we do. It makes Nick sad to have to disappoint Harry like this, but he can't help it. This whole exchange between Harry and Nick is interesting because (a) it's terribly sad to watch Harry struggling with his godfather's death, and (b) it shows how death fits into the magical world. Even though the wizarding community is filled with wonders, they still haven't managed to unlock the secrets of life and death. Rowling is drawing a line to show what magic can and cannot do: Nick demonstrates that there are still mysteries beyond the knowledge even of wizards, and death is one of them.