The reason is because any atomic orbitals and molecular orbitals with appropriate symmetry can interact with each other, and the strength of the interactions between orbitals depends on their respective energy separations. Those various energy gaps change as a function of the nuclear core charges and electron configurations. I.e., orbital interactions are complex, even for diatomics. The end result is that there comes a point going down the series X
2 where the ordering of the 2p pi and sigma bonding orbitals flip (the 2pi bonding orbitals are lower in energy than the 2sigma bonding orbitals). See the diagram at
this link, under the section "Quantum patterns". Of those with a "flipped" configuration, dinitrogen is really the only one with practical relevance.
EDIT: Basically, the atomic s orbitals don't just interact with atomic s orbitals, even though we erroneously draw MO diagrams that way in introductory chemistry. Atomic s orbitals also have the appropriate symmetry to interact with atomic p
z orbitals if they are aligned appropriately, which they are in a diatomics. Sometimes those interactions are strong, sometimes not, depending on the mutual energies of the s and p orbitals. And those energies change quite a bit as the nuclear core charges change going across the periodic table. The bonding sigma orbital energies are very sensitive to this, as you can see in the diagram linked to above.