Why do you say you can't use aggregate functions on dates? SUM isn't the only aggregate available.

Is this what you need?

Select @SQL2 = ISNULL(@SQL2+',','') + CHAR(13)

+ 'MAX(CASE WHEN CalendarDate = '''

+ CAST(CalendarDate as varchar(15)) + ''' THEN CalendarDate END) AS ['

+ CAST(ISOWeek as char(2)) + ']'

From #Dates

Or like this which gives the same result but uses the columns differently

Select @SQL2 = ISNULL(@SQL2+',','') + CHAR(13)

+ 'MAX(CASE WHEN ISOWeek = '

+ CAST(ISOWeek as varchar(2)) + ' THEN CalendarDate END) AS ['

+ CAST(ISOWeek as varchar(2)) + ']'

From #Dates