There are 2 general kinds: absolute/perfect pitch & relative pitch. The main difference is in relative pitch, you can identify the note after another note has been played; this is the reference note & you are able to identify the 2nd note by distinguishing the relative difference in frequencies between these 2 notes. In perfect pitch however, you can identify the note without needing the reference note.
Well apparently there are several explanations for these abilities. Having perfect pitch is like having a special ear, where you can remember the frequency of a certain note & you can recall it when you hear it. It's like remembering how the color blue is different from purple, or how to distinguish the 4 tones in Mandarin (see this banana knows some stuff!). It's a cognitive trait, believed to have a genetic basis. I recall reading somewhere that only 1 in 10 people has this skill.
Can this skill be taught? Only if the talent already resides inside of you, musical training will certainly help to enhance this skill. There are special courses designed to hone this skill, you just need to know where to look for them. This skill is also prevalent among individuals who were blind from birth, has Williams Syndrome or an autism spectrum disorder. Seems like a genetic thing to me.
My personal experience is this skill has helped me a lot in playing & writing music. It enhances my listening experience; singing along note by note. I'd say that practice does help; I find my hearing becomes rusty if it's not used frequently enough. I don't find it a hassle to listen to transposed instruments (like the B flat trumpet); it takes just a bit more effort to transpose & get the note(s) down. I've to thank Mom & Dad for this gift :)