TL;DR- there are timbral differences in different keyed trumpets (as you’d expect), but it mainly has to do with the key in which the music is written. Trumpet players do a lot of transposing, so having a trumpet in D is handy for playing Handel and other composers from that era (usually their stuff for trumpets is written in D), one in E-flat is handy for the Haydn and Hummel trumpet concertos (there’s actually a lot of back-and-forth among trumpeters whether the Hummel should be played in E-flat or E). B-flat is usually what is played in wind bands and for jazz. C is usually for symphonic work. Others are out there for those who want to specialize (like trumpets in F specifically for the Bach Brandenburg no. 2). Piccolo trumpets are usually convertible between B-flat and A by using different lengths of leadpipe (the tube you put the mouthpiece into) and they have the most unique sound.