I think that I can convert this into an inline table-valued function. That would at least allow parallelism and won't be called one row at a time.
I just need...

Avoid showing the results in the grid and just show them on text. That might help to reduce memory consumption by SSMS.

Is it basically the same  behavior shown here?
https://www.brentozar.com/archive/2017/09/ghosts-temp-tables-past/

For a dynamic approach, check the following article:
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

For that, you need to unpivot your values and pivot them again.
I'm not using PIVOT and UNPIVOT operators, although , you could use them. I prefer the alternatives that...

You can get information about temp tables' scope in here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql#temporary-tables

You seem to be relatively new to the forums, but you should take into account that it's a good courtesy to post your sample data in a consumable format. I'll...