That would also mean that I have to take the time to create some voluminous test data. It's your turn to do that. 
Also, apologies for the snap but I've been getting snapped at a whole lot and thought I'd start returning the "favor". 
One of the things I'm thinking about in the code is that it's a well proven fact that "pre-aggregation" can add a whole lot of performance to code and that's where I was headed with my recommendation.
You don't need "voluminous" test data. The few rows @yosiasz posted will do. Add a few more if you like/need to demonstrate other data pattern(s) that are important. I'm 100% not interested in generating data myself. (Edit: added "myself" for clarity. I'm not opposed to someone using lots of data, I just don't have any interest in manufacturing that data myself.)
Not true. Performance is a very, very close second to accuracy.
declare @sqlfor table(col1 char(1), col2 int, col3 int)
insert into @sqlfor
select top 100000 upper(left(name,1)), column_id, system_type_id
from sys.all_columns
select * From @sqlfor
Thanks for posting the data generation script, Tito but I was hoping Scott would finally do one. Heh... he really has some sort of dislike for doing it.
Shifting gears a bit, not only a nice job on leveraging sys.all_columns as a row source but also nice job using the data within it to match the given problem. The only problem is that it doesn't typically generate 100,000 rows without a cross join to itself. On my 2017 Developer's edition in a new database, it only generates a bit under 10,000 rows.
Since you were kind enough to "provide the seed", I modified it a bit to support a larger test and here's the result. Thanks again for providing the seed code.
DROP TABLE IF EXISTS #SQLFor;
CREATE table #SQLFor
(Col1 CHAR(3), Col2 INT, Col3 INT, Col4 CHAR(2))
;
INSERT INTO #SQLFor WITH (TABLOCK)
SELECT TOP 1000000
Col1 = UPPER(LEFT(ac2.name,3))
,Col2 = ac2.column_id
,Col3 = ac2.system_type_id
,Col4 = right(ac2.name,2)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;Cool, I was trying to base it on one you yourseld had provided a while back but couldn't find it.
I think you @JeffModen and @ScottPletcher both bring a lot to the table, so please lay off with the back and forth. Lets end 2020 on a good note. Technlogoy comes and goes, but people will always be around. I know I learn a lot of technical gems from you both
Yes. You're correct and thank you for the reminder, Tito. I'll be back tonight with some (what I think) are some interesting offshoots for problem #2.
I disagree with Jeff's basic premise. Not every query needs to be run against 1M rows. And the opportunity cost of doing so is very damaging to the overall performance of the instance. I.e., there are almost always more productive things people could be doing.
We don't know this person's data. We don't even know if there is real data. This could be just a theoretical q. I have limited time available. Why spend that time on something that is likely 100% not relevant to the OP?
And the OP has never come back to let us know 
what he wants and what his data looks like 
Would still love to see your other approaches / offshoots on #2.
Oh, yeah... I have code but you wouldn't be interested, Scott. You can't see testing things against a million rows.
I'm interested in the code itself, the "interesting offshoots for #2". Others might be very interested in the 1M rows part of it.