Need help converting Dynamic Pivot output to XML / HTML

  • G'day. I need some help converting the output from a pivot statement to HTML. I can't seem to get my head around to do this dynamic.

    Here's my code. Any help would be highly appreceated.

    DECLARE@DynamicPivotQueryNVARCHAR(MAX)

    ,@ColumnNameNVARCHAR(MAX)

    ,@ColumnName1NVARCHAR(MAX)

    ,@JaarVarChar(12)

    ,@BodyVarChar(Max)

    ,@SqlCmdnVarChar(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)

    --Prepare the PIVOT query using the dynamic

    SET @DynamicPivotQuery =

    N'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

    order by ivt_site

    '

    EXEC sp_executesql @DynamicPivotQuery

    DROP TABLE #invtrend

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply