Have you considered any particular table yet? The thing is that there are table variables and temporary tables. I use both extensive in many reports, analytics. Mainly for speed, simplicity and maintainability. I use that resource for mssql temp table https://codingsight.com/introduction-to-temporary-tables-in-sql-server/ and feel pretty comfortable about that. Simply give it a try

You may need temporary staging tables for reports that visit several databases. At the moment, I have a bunch of reports that visit about several dozen database instances of several different types. Initial queries run on each db, fetch data into temporary tables in the reporting db, and the final report query runs on these temporary tables. This “reporting structure” would be very hard to code without using temporary tables.

What are the situations that temporary tables are used in SQL?