You will after reading this: The "Numbers" or "Tally" Table: What it is and how it replaces a loop

I'm doing a lot of guessing here; moving forward - DDL and sample data will help you get the best answer.  Let's start with this:

USE tempdb

Does the reportcurrently use the stored proc or is this a new report? I ask because, if youhave a report that starts with a stored proc but is changed to...

This is an fun and interesting problem. If my solution is what you are looking for (or close enough) then I would suggest this index:
CREATE CLUSTERED INDEX...

Just for fun I thought I'd throw in a solution that leverages NGrams8k. This will likely be a little slower than what Jason posted but it parallelizes nicely...

You can do this on SQL Server 2005+ like so:

-- Your Data
DECLARE @table TABLE(ColA INT, ColB CHAR(1), RowNumber INT);
INSERT @table(ColA,ColB,RowNumber)
SELECT ColA,ColB,RowNumber
FROM (VALUES

They worded this BOL...