This couldn't have come at a better time, Wayne & I had something similar this week. Can you post the ddl for the table, along with a few INSERTs to get some sample data into it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden