July 24, 2014 at 3:41 am
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
July 25, 2014 at 6:46 am
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