You really want to look at a pivot table for this.

Review these articles:

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Fraggle