SQL Server 2008 – Convert Flat Query to Column Structure

I am trying to structure a query for a calendar view.

I want each row to return the details for a person and what they are doing for 7 days (split into AM and PM)

So the result set might look like this:

Name - Day1AM - Day1PM - Day2AM - Day2PM (...more days across the columns)
"John Smith" Event1 Event1 <blank> <blank> 
"Bill Jones" <blank> <blank> Event1 Event1 

Each day section could have no data:

EDITED to add scripts:

DDL Script and Data:

CREATE DATABASE [TestPivot] ON  PRIMARY 
( NAME = N'TestPivot', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestPivot.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestPivot_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestPivot_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Use [TestPivot]
Go

CREATE TABLE [dbo].[Person](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Event](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] 


CREATE TABLE [dbo].[DayPart](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EventID] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_DayPart] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DayPart]  WITH CHECK ADD  CONSTRAINT [FK_DayPart_Event] FOREIGN KEY([EventID])
REFERENCES [dbo].[Event] ([ID])
GO

ALTER TABLE [dbo].[DayPart] CHECK CONSTRAINT [FK_DayPart_Event]
GO

GO

CREATE TABLE [dbo].[PersonDayPart](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PersonID] [int] NOT NULL,
    [DayPartID] [int] NOT NULL, 
 CONSTRAINT [PK_PersonDayPart] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PersonDayPart]  WITH CHECK ADD  CONSTRAINT [FK_PersonDayPart_DayPart] FOREIGN KEY([DayPartID])
REFERENCES [dbo].[DayPart] ([ID])
GO

ALTER TABLE [dbo].[PersonDayPart] CHECK CONSTRAINT [FK_PersonDayPart_DayPart]
GO

ALTER TABLE [dbo].[PersonDayPart]  WITH CHECK ADD  CONSTRAINT [FK_PersonDayPart_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([ID])
GO

ALTER TABLE [dbo].[PersonDayPart] CHECK CONSTRAINT [FK_PersonDayPart_Person]

--person data
INSERT INTO [Person] ([FirstName] ,[LastName])
VALUES ('John' , 'Smith')
INSERT INTO [Person] ([FirstName] ,[LastName])
VALUES ('Jean' , 'Jones')

--event data
INSERT INTO [Event] ([Title])
VALUES('Course 1' )
INSERT INTO [Event] ([Title])
VALUES('Course 2' )

-- day part data
DECLARE  @dayPart1ID int, @dayPart2ID int, @dayPart3ID int, @dayPart4ID int
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '1/1/2012')
select @dayPart1ID = @@identity
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '2/1/2012')
select @dayPart2ID = @@identity
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '3/1/2012')
select @dayPart3ID = @@identity
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '3/1/2012')
select @dayPart4ID = @@identity

--Person Day Parts for @dayPart1ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (1,@dayPart1ID)
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart1ID)


--Person Day Parts for @dayPart2ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart2ID)


--Person Day Parts for @dayPart3ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart3ID)
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (1,@dayPart3ID)


--Person Day Parts for @dayPart4ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart4ID)
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (1,@dayPart4ID)

Ideally I could pass the query a start date and it would use that as the basis for the 7 days.

I am aware of Pivot and using case statement methods, but have really struggled trying to piece this together.

Edit: Here is my attempt at a pivot, it seems to work ok:

select * 
from 
( 
    select firstname,[Date], e.Title as stringvalue 
    from    
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID  
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID 
              LEFT outer JOIN
        dbo.Event e ON e.ID = idp.DayPartID
) as d 
pivot 
( 
    min([stringvalue]) 
    for [Date] in ([2012-01-01], [2012-02-01], [2012-03-01], [2012-04-01], [2012-05-01], [2012-06-01],[2012-07-01]) 
) 
as p 

What I would like to know is if it is possible to set the dates in the FOR section dynamically in some fashion based on a start date passed into a stored procedure.