Mechanism To Dynamically Build Pivot Table

,

This code provides a mechanism to generate dynamic pivot tables.  In this case, we're checking backup history to X months, checking average backups each month.
There is a @basequery that can be anything, so long as you place two tags for columns and list, surrounded by << >>.
In this example, if you change @numMonths, you will see those values evolve in the loop if you uncomment the DEBUG: lines.
/*
  This script is hopefully a useful contribution to dynamically generating a pivot query.  The workhorse of this is a pivot query
  that has a <<COLUMNS>> and <<LIST>> place holder for @Codeblock1 and @Codeblock2.  Everything else can be customized as necessary,
  but in this case, using average backups for the last N months.
*/

declare @basequery nvarchar(max),
		@finalquery nvarchar(max),
		@ctr int

set @basequery =
';WITH BACKUPS AS
(
SELECT
	ID = ROW_NUMBER() over (order by [database_name], DATEPART(month,[backup_start_date])),
	[database_name] AS DBName,
	DATEPART(month,[backup_start_date]) AS "Month",
	AVG([backup_size]/1024/1024) AS "BackupSizeMB"
FROM msdb.dbo.backupset
	WHERE [type] = ''D''
GROUP BY [database_name],DATEPART(mm,[backup_start_date])
)
select
	NextBack.[ID],
	NextBack.Month, NextBack.DBName, NextBack.[BackupSizeMB]
into #dump
from BACKUPS as NextBack
	order by NextBack.ID

;with CTE as
(select
	DBName [DBName (and sizes in MB)], <<COLUMNS>> -- this part has the dynamic list of columns to view, in this case, months.
from
	(select [Month], [DBName], cast([BackupSizeMB] as decimal(10,1)) [BackupSizeMB] from #dump) as SourceTable
	PIVOT
	(max(BackupSizeMB) for Month in (<<LIST>>)) as MyPivotTable -- this part says to select from the columns.
)
select * from CTE order by 1

drop table #dump'

declare @codeBlock1 nvarchar(max), -- block of code to use to fill in the << column >> field below
		@codeBlock2 nvarchar(max), -- block of code to use to fill in the << list >> field below
		@numMonths int = 2, -- default to this month + last 2 months
		@tgtMonth datetime,
		@currMonth int = datepart(month, getdate())

-- Set the Columns and values you want to display
set @codeBlock1 = '' -- Use to build Columns
set @codeBlock2 = '' -- Use to build the in list of the pivot query

set @ctr = @currMonth - @numMonths  -- some sort of loop.  In this case, counting months.  Yes, I know this may break if it loops back to the year before. :)

while (@ctr <= @currMonth)
begin
	set @tgtMonth = dateadd(month, -1*(@currMonth - @ctr), getdate())

	set @codeBlock1 = @codeBlock1 +
		quotename(cast(@ctr as varchar(2))) + ' AS ' + quotename(datename(Month, @tgtMonth) + ' ' + datename(year, @tgtMonth))
	if (@ctr != @currMonth)
		set @codeBlock1 = @codeBlock1 + ', '

	set @codeBlock2 = @codeBlock2 +
		quotename(cast(@ctr as varchar(2)))
	if (@ctr != @currMonth)
		set @codeBlock2 = @codeBlock2 + ', '

	-- DEBUG:
	-- print cast(@tgtMonth as varchar(max))
	-- print @codeblock1
	-- print @codeblock2
	set @ctr = @ctr + 1
end
set @finalquery = replace(replace(@basequery, '<<COLUMNS>>', @codeBlock1), '<<LIST>>', @codeblock2)

print(@finalquery)
EXEC(@finalquery)

Rate

5 (1)

Share

Share

Rate

5 (1)