Greeting Grasshopper;

I think what you are looking for is a windowing function also called 'over'

Take a peek at this and see if this is what you are looking for. https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql