Have a look at ROW_NUMBER() OVER() and Common Table Expressions. These will allow you to accomplish what you need to do. If you need any help let us know and show us what you have.
http://msdn.microsoft.com/en-us/library/ms186734.aspx ROW_NUMBER()
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx Common Table Expressions.
Also great sample data and ddl.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]