Denormalizing Via SELECT

I have two tables related many-to-many so there's an intersection table in the middle. The first table is dbo.Event. It has a list of gigs (events) my company has booked to provide services for. The second table is dbo.Employee which has a list of employees. The intersection table dbo.EventEmployee contains which employee(s) have been scheduled to work. The intersection table has some status information whether the employee asked to work, is scheduled to work, etc. The data type is TINYINT

Here's what I want to do. Create a SELECT statement that basically denormalizes these three tables so the recordset looks like this:



Event.Name, Event.StartDate, Event.Location, Ed, Mary, Tom, Steve, Sally



The idea is to get the list of employees to become columns in the result set. If an employee is scheduled for the event there is a non-null value (Status), if an employee isn't scheduled there is a NULL in their column. For example:



CDC, 2/12/07, Chicago, NULL, 0, 0, NULL, NULL

NYC, 11/04/07, New York, 0, 0, 0, 0, NULL



The first record Mary & Tom are scheduled to work the CDC. The second row everyone except Sally is scheduled to work.



I've thought of cursor based solutions but performance will be rather poor. There are multiple places I want to use this type of solution so I'm hoping there's a more elegant way to accomplish this. SQL 2000 with latest SP. Help. This is beyond my SQL abilities.



harrier