I wrote it now, so despite Luis excellent answer I am posting it - seems a waste otherwise 😛

-- OP Setup Code --

If Object_Id('TempDB..#Editions', 'U') Is Not Null

Drop Table #Editions

Create Table #Editions

(

Edition_ID Int Primary Key Clustered,

Edition_Year int,

Edition_Start datetime,

Edition_Finish datetime

)

Set Dateformat DMY

Insert into #Editions

(Edition_ID, Edition_Year, Edition_Start, Edition_Finish)

Select '1', '2012', '27-07-2012', '11-08-2012' UNION ALL

Select '2', '2008', '07-08-2008', '23-08-2008'

-- second table #Events

If Object_Id('TempDB..#Events', 'U') Is Not Null

Drop Table #Events

Create Table #Events

(

Event_ID Int Primary Key Clustered,

Event_Name nvarchar(10),

Event_Year int,

Event_Start datetime,

Event_Finish datetime

)

Set Dateformat DMY

Insert into #Events (Event_ID, Event_Name, Event_Year, Event_Start, Event_Finish)

Select '1', 'AA', '2012', '04-08-2012','07-08-2012' UNION ALL

Select '2', 'BB', '2012', '07-08-2012','10-08-2012' UNION ALL

Select '3', 'CC', '2012', '04-08-2012','06-08-2012' UNION ALL

Select '4', 'DD', '2012', '31-07-2012','01-08-2012' UNION ALL

Select '5', 'EE', '2012', '27-07-2012','03-08-2012' UNION ALL

Select '6', 'AA', '2008', '17-08-2012','20-08-2012' UNION ALL

Select '7', 'BB', '2008', '12-08-2012','13-08-2012' UNION ALL

Select '8', 'CC', '2008', '20-08-2012','20-08-2012'

-- End OP Setup Code

DECLARE @SelectedYear CHAR(4) = '2012';

-- Make sure we don't get any errors with a temp table

If Object_Id('TempDB..#days', 'U') Is Not Null

Drop Table #days

-- Generate a list of days for the selected Edition

select dateadd(day,T.N,ed.edition_start) as Event_Day,CONVERT(char(5),dateadd(day,T.N,ed.edition_start),5) as Event_Day_Label

into #days

from #Editions ed

join Tally T

on T.N between 1 and datediff(day,ed.Edition_Start,ed.Edition_Finish)

where ed.Edition_Year=@SelectedYear

-- Generate a SELECT column list that will populate the Xs

declare @cols varchar(max)='';

SELECT @cols = (SELECT ', CASE WHEN '''+CONVERT(CHAR(10),Event_Day,112)+''' BETWEEN [Event_Start] AND [Event_Finish] THEN ''X'' ELSE '''' END AS ['+Event_Day_Label+']'+CHAR(13) FROM #days ORDER BY Event_Day FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)');

-- Generate the dynamic SQL

declare @sql varchar(max)='';

SELECT @sql = '

SELECT Event_Name'+@cols+'

FROM #Events

WHERE Event_Year='''+ @SelectedYear +'''

ORDER BY Event_Name';

-- EXECUTE the dynamic SQL

EXEC(@sql);

And the results:

+------------------------------------------------------------------------------------------------------------------------------------+

¦[highlight="#808080"] Event_Name [/highlight]¦[highlight="#808080"] 28-07 [/highlight]¦[highlight="#808080"] 29-07 [/highlight]¦[highlight="#808080"] 30-07 [/highlight]¦[highlight="#808080"] 31-07 [/highlight]¦[highlight="#808080"] 01-08 [/highlight]¦[highlight="#808080"] 02-08 [/highlight]¦[highlight="#808080"] 03-08 [/highlight]¦[highlight="#808080"] 04-08 [/highlight]¦[highlight="#808080"] 05-08 [/highlight]¦[highlight="#808080"] 06-08 [/highlight]¦[highlight="#808080"] 07-08 [/highlight]¦[highlight="#808080"] 08-08 [/highlight]¦[highlight="#808080"] 09-08 [/highlight]¦[highlight="#808080"] 10-08 [/highlight]¦[highlight="#808080"] 11-08 [/highlight]¦

+------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------¦

¦ AA ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ X ¦ X ¦ X ¦ X ¦ ¦ ¦ ¦ ¦

[highlight="#E0E0E0"]¦ BB ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ X ¦ X ¦ X ¦ X ¦ ¦[/highlight]

¦ CC ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ X ¦ X ¦ X ¦ ¦ ¦ ¦ ¦ ¦

[highlight="#E0E0E0"]¦ DD ¦ ¦ ¦ ¦ X ¦ X ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦[/highlight]

¦ EE ¦ X ¦ X ¦ X ¦ X ¦ X ¦ X ¦ X ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦

+------------------------------------------------------------------------------------------------------------------------------------+