It was a difficult one, but I hired a consultant to do the legwork, as I couldn't find the time to do it myself

Here's his (working) solution. All credits go to Ray Chan at FMA.

DECLARE @DynamicPivotQuery NVARCHAR(MAX)

, @ColumnName NVARCHAR(MAX)

, @ColumnName1 NVARCHAR(MAX)

, @ColumnName2 NVARCHAR(MAX)

, @Header NVARCHAR(MAX)

, @Jaar VarChar(12)

, @Body VarChar(Max)

,@Result varchar(Max)

, @SqlCmd nVarChar(Max)

SELECT @Jaar = Convert(VarChar(12),Year(GetDate()))

SELECT * INTO #invtrend

FROM (

SELECT N'2014/29' AS [ivt_week], N'108' AS [ivt_site], N'54.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'08' AS [ivt_site], N'66.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'108' AS [ivt_site], N'12.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'109' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'11' AS [ivt_site], N'11.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'13' AS [ivt_site], N'81.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'15' AS [ivt_site], N'0.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'18' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'19' AS [ivt_site], N'3.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'22' AS [ivt_site], N'35.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'31' AS [ivt_site], N'56.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'35' AS [ivt_site], N'0.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'50' AS [ivt_site], N'16.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/28' AS [ivt_week], N'60' AS [ivt_site], N'35.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'109' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'11' AS [ivt_site], N'5.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'13' AS [ivt_site], N'53.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'15' AS [ivt_site], N'0.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'18' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'19' AS [ivt_site], N'53.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'22' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'31' AS [ivt_site], N'7.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'35' AS [ivt_site], N'19.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'3765' AS [ivt_site], N'203.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'50' AS [ivt_site], N'53.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/29' AS [ivt_week], N'60' AS [ivt_site], N'20.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'08' AS [ivt_site], N'88.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'108' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'109' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'13' AS [ivt_site], N'27.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'15' AS [ivt_site], N'0.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'18' AS [ivt_site], N'11.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'19' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'22' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'31' AS [ivt_site], NULL AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'35' AS [ivt_site], N'16.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'50' AS [ivt_site], N'49.0000' AS [ivt_davgm] UNION ALL

SELECT N'2014/30' AS [ivt_week], N'60' AS [ivt_site], N'32.0000' AS [ivt_davgm] ) t;

--Get distinct values of the PIVOT Column

SELECT @ColumnName= ISNULL(@ColumnName + ',' , '') + QUOTENAME(ivt_week)

FROM (SELECT DISTINCT ivt_week FROM #invtrend WHERE Left(ivt_week,4) = @Jaar) AS weken

select @ColumnName1 = COALESCE(@ColumnName1,'') + QUOTENAME(ivt_week, '''') +' = '+QUOTENAME(ivt_week)+','

FROM (SELECT DISTINCT ivt_week FROM #invtrend WHERE Left(ivt_week,4) = @Jaar) AS weken

select @ColumnName1 = Left(@ColumnName1, Len(@ColumnName1)-1)

SELECT @ColumnName2= ISNULL(@ColumnName2 + '+''</td>''+' , '') + '''<td>''+ISNULL(CONVERT(VARCHAR(30),'+QUOTENAME(ivt_week)+'),'''')'

FROM (SELECT DISTINCT ivt_week FROM #invtrend WHERE Left(ivt_week,4) = @Jaar) AS weken

SELECT @ColumnName2 = @ColumnName2+'+''</td></tr>'''

SELECT @ColumnName2

-- ISNUL om geen null eruit te krijgen

-- CONVERT naar varchar

-- Table Header

SELECT @Header = COALESCE(@Header,'') + '<th>'+ivt_week +'</th>'

FROM (SELECT DISTINCT ivt_week FROM #invtrend WHERE Left(ivt_week,4) = @Jaar) AS weken

SET @Header = '<tr><th>Regio</th>'+@Header+'</tr>'

--SELECT @Header

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

N'

WITH cteRegio (Regio, '+@ColumnName+') AS

(

SELECT Regio = ivt_site ,'+@ColumnName1 + '

FROM (SELECT ivt_week, ivt_site, Convert(Money,ivt_davgm,0) as ivt_davgm FROM #invtrend) AS Bron

PIVOT(AVG(ivt_davgm) FOR ivt_week IN (' + @ColumnName + ')) AS PVTTable

)

SELECT @Result = ISNULL(@Result,'''')+''<tr><td>''+Regio+''</td>''+'+@ColumnName2+' FROM cteRegio ORDER BY Regio

'

--SELECT @Result = ''<tr><td>''+Regio+''</td>'', '+@ColumnName2+' FROM cteRegio ORDER BY Regio

--SELECT @Result = ISNULL(@Result,'''') +'' ''+ Regio FROM cteRegio ORDER BY Regio

EXEC sp_executesql @DynamicPivotQuery , N'@Result varchar(max) out', @Body out

SELECT '<table>'+@Header+@Body+'</table>'

SELECT @DynamicPivotQuery

DROP TABLE #invtrend