The title would be graphic designer or lettering artist. Any good graphic designer should be able to do something like that, or a custom letterer, depending on whether you need … well, custom lettering (as opposed to an existing typeface).

I guess the design you refer to was made applying a special effect program.
But there are other way to do it. Yes, any good designer should be able …

You would laugh if you’d know how I made this one:
(I’ll never gonna tell you)
.

While I agree that any graphic designer should be able to take a crack at it, that kind of highly-dramatic 3D lettering is not something that's needed very often, so some designers wouldn't have much experience with it. So I'd look carefully at their portfolios to see if they have nice examples showing similar techniques. Many designers would subcontract an illustration like that out to a commercial illustrator who had a lot of experience with that type of lettering.

Check out my brother's work, specifically this piece. I'm obviously biased, but I think he's capable of what you need. You can contact him at alecs23 [at] gmail [dot] com.