Most video editing packages have a titling capability to some degree, but if you want to do anything really fancy, they struggle breath on the steeper hills. We have just received a copy of the latest NewBlue Titler Pro 6, and within minutes, had grasped its possibility and cleverness.
Here is a quick tutorial from the NewBlue website to give youi an idea. You can download a free trial from Download NewBlue Titler Pro 6 trial.