While it doesn't seem to be explicitly spelled out anywhere, the Sneak Attack entry says:
Beginning at 1st level, you know how to strike subtly
and exploit a foe’s distraction. Once per turn, you can
deal an extra 1d6 damage to one creature you hit with
an attack if you have advantage on the attack roll. The
attack must use a finesse or a ranged weapon.
This is extra damage dealt by the weapon attack; therefore, it should deal damage of the same type as the weapon. As backup for this, the section on critical hits says that:
For example, if you score a critical hit with a dagger, roll 2d4 for the damage, rather than 1d4, and then add your relevant ability modifier. If the attack involves other damage dice, such as from the rogue’s Sneak Attack feature, you roll those dice twice as well.
It seems clear that Sneak Attack is extra damage added to the weapon attack, and therefore has the same damage type as the weapon.
Specifically, the Spy uses a shortsword, which deals piercing damage, so his Sneak Attack damage will be piercing also.
No.
There are no weapons that just straight-up switch their damage type like that, and no spells or class abilities that can allow it.
There are weapons such as a Flame Tongue or Frost Brand which can deal additional elemental damage of a particular type. Then there are weapons like a Sun Blade which exclusively deals Radiant damage instead of Slashing damage.
As was mentioned in the comments, spells like Elemental Weapon or runes from Storm King's Thunder also exist...but they, likewise, supplement your damage, rather than entirely changing the type.
There is also the Sun Soul Monk, who can elect to trade out their Unarmed Strikes for energy blasts that deal Radiant Damage...but this is an alternate form of attack, not actually changing a weapon's damage type.
Ultimately, if you want a weapon that can change the sort of damage it deals, you're in homebrew territory.
Tangential Advice
There's nothing wrong with supplementing damage with an Elemental Type, rather than replacing it entirely. At least then you're doing something to contribute to the damage dealt. Perhaps carry extra gear that lets you nonmagically mimic elemental damage types, such as Alchemist's Fire, Acid Flasks, Poisons, etc.
Best Answer
Maybe
First, Crawford did tweet under Sage Advice that
This then leads us to the question of what damage type Sneak Attack should use.
In support of Primary type of damage
As you linked, Sneak Attack delivers damage of the primary type of the weapon (piercing, slashing, etc.)
The Flame Tongue states that it, (DMG, 170):
emphasis mine
Jeremy Crawford also supports this with his Sage Advice answer:
Therefore, the sneak attack damage of the Flame Tongue is that of the primary type of your flame tongue (whatever it may be) and NOT the fire damage.
In support of Choosing your damage type
Crawford suggests you may have a choice, though, in cases of multiple damage types
In another question about Hunters Mark Damage, Crawford answers somewhat differently:
I don't think those two responses are at odds. You must use the damage type of the weapon, but if the weapon/strike delivers multiple damage types then you may pick. The Flame Tongue would give you that option for fire damage on Sneak Attack if you extrapolate this ruling on Hunters Mark to Sneak Attack.