Seems like a pivot would probably work for what you have described.

You can see an example here. http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

With a table structure (ddl) and more consumable data, somebody could probably help in more detail.