One of the only parts I remember from No More Heroes was the fight with Dr. Peace(?). As you enter the baseball field, he sings a pretty good song.
Every hymn in Ar tonelico / Ar nosurge. The story of the games revolves around song magic, so anything that happens has a song/performance attached to it. The downside being most of the songs are performed in a language they invented for the game, so you get to go and look up fan translations after the fact.
Ar tonelico 3 - EXEC_FLIP_ARPHAGE/. (wiki)
Ar tonelico 2 - EXEC_with.METHOD_METAFALICA/. (wiki)
Ar tonelico - EXEC_HARMONIUS/. (wiki)
Ar nosurge - Class::EXSPHERE_NOSURGE; (wiki)
Ciel nosurge - QuelI->EX[cez]->{kranz}; (wiki)
Adding to that, 2 repeats the trick with the Margaret boss fight. You know absolutely nothing about her, even less so than any other boss, and she barely has any lines. However, the game suggests she’s actually singing the boss theme as you fight her, and that’s where all the information on her is.
Also, how has NOBODY mentioned the masterpiece that is the GOD HAND THEME SONG!? (warning: very, very loud)
Can you elaborate on this a bit? I can’t find any footage of characters actually singing - is this a game mechanic or something overlaid on the soundtrack during combat?
Assassin’s Creed Syndicate has 5 in-game songs, dubbed “Murder Ballads” that are about five of your main assassination targets in the game.
Unfortunately they’re hard to find in-game because pedestrians and street choirs sing them, completely at random. I managed to find one instance on YouTube https://www.youtube.com/watch?v=D1thJdWb56k
But there’s four other songs, their studio recorded equivalents are on the OST but they obviously don’t sound like they sound in-game :-/
Most of the hymns show somebody singing in some capacity, as far as I can remember–though because every song is sung for a purpose, the scenes will typically focus on what the songs are doing as opposed to the performances themselves. They’ll occasionally play over boss fights as well with the implication that somebody is singing. The games are old and not especially popular so digging through (mostly Japanese) let’s plays is probably the only place you’ll find that sort of footage; most people just post fan translations of the songs themselves.
METHOD_HYMELLI/.
EXEC_FLIP_ARPHAGE/.
EXEC_VIENA/.
Ciel nosurge focused the most on the performance aspect, but was never released in English.
The Shopkeeper in Crypt of the Necrodancer is the one that comes to mind most immediately for me.
For narrative there is The Great Might Poo boss battle from Conker’s Bad Fur Day or the Phantom Opera battle from Mario + Rabbids Kingdom Battle which are basically both the same gag.
Final Fantasy XIV has a great example in the Stormblood expansion with “The Measure of His Reach”. Spoilers under the cut.
The significance of “The Measure of His Reach” is that it is the anthem of Ala Mhigo, which was under Garlean rule for decades. The Garleans even made their own propaganda version with lyrics extolling their might. In this video Ala Mhigo has just been freed, so the characters being able to sing their own theme is a pretty powerful storytelling moment.
Found another example from Adventure Time: Pirates of the Enchiridion - gotta love those sea shanties 
I’d like to add the singing from the Dragon’s Sanctum in Dark Souls 2: The Crown of the Sunken King. Both from Elana (below) and the sanctum priestesses.
I’m still kind of digesting Dark Souls 2 and have mixed feelings on it, but the DLC is phenomenal and the mood this singing creates is so fucking eerie. Real good shit.
Also, because I use any chance I can to talk about Beeswing (bought/played for free here)
There’s a scene where you can walk into a bar and you see a drawing of the musician (Silverley Allen) while a live recording of this song plays:
It’s extremely good and magical and I really really like Beeswing
Edit: here’s a picture
I immediately think of The Song of the Ancients from Nier.
You first hear this being sung by the character Devola, and even though you can’t understand the lyrics its melancholy and beauty make it immediately stand out.
Eventually you realize that this is the main theme of the game, remixed many times, sometimes faster tempo, sometimes slower, but always retains that edge of sadness.
The Shrine of Amana in Dark Souls 2 features diegetic singing that ties into the gameplay. When the singing stops, a class of enemy wakes up and is ready to aggro any player in range.
Also! There are two missions in HITMAN: Blood Money that feature singing: “Curtains Down”, which takes place in an opera house, and “Dance with the Devil”, which takes place in a weird rich goth party. (Warning: Blood Money has a lot misogyny and sexualization in it that’s really evident in the latter mission.)
The opera Tosca is in “Curtains Down”:
Eve singing “Tomorrow Never Dies” by Swan Lee in “Dance with the Devil”:
Damn, can’t believe I forgot about the twins! 
Huge spoiler for Oxenfree, but in the second to last scene of that game here there is some diegetic singing from a ghost over the radio
Considering I played this at the time, I’m kind of sad it was so forgettable:
there’s also Semi Sacred Geometry sung by Danielle Sho and her band from Prey (2017) 
Can’t believe I forgot this LEGENDARY sequence and the absolute banger that is Semi-Sacred Geometry
In the Horizon Zero Dawn DLC there is a sidequest where you pick up tapes about a amateur punk band, formed by the scientists working in the facility. They even use the pipes in the plant for drums. In the current day the same pipes are seen as a sacred instrument by some.
Here’s the song: https://www.youtube.com/watch?v=WsOMfKDZHD4
