Certified GKH Gardening Expert
You can bake a pie from almost any winter squash (pumpkins being the most popular), but flavor wise, some squash are better than others. When talking about your standard "pumpkin" - orange, round winter squash, a sugar pumpkin (which is the small one they sell in the grocery store), will give you the best flavor. Jack-o'-lantern pumpkins can also be used to make pies and will taste ok, just not as good as a sugar pumpkin.
The "pumpkin" you buy in a can is normally not the pumpkin you think of. It is usually a more economically grown squash, like a Hubbard squash. As there is no hard, legal definition of a pumpkin and the term is used to refer to a wide variety of winter squash, so as long as it tastes like a pumpkin - it can be sold as pumpkin.
To be very honest, I like to make pumpkin pie with butternut squash. The flavor and texture are excellent for pies.