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),
set @basequery =
';WITH BACKUPS AS
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"
WHERE [type] = ''D''
GROUP BY [database_name],DATEPART(mm,[backup_start_date])
NextBack.Month, NextBack.DBName, NextBack.[BackupSizeMB]
from BACKUPS as NextBack
order by NextBack.ID
;with CTE as
DBName [DBName (and sizes in MB)], <<COLUMNS>> -- this part has the dynamic list of columns to view, in this case, months.
(select [Month], [DBName], cast([BackupSizeMB] as decimal(10,1)) [BackupSizeMB] from #dump) as SourceTable
(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
@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)
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 + ', '
-- print cast(@tgtMonth as varchar(max))
-- print @codeblock1
-- print @codeblock2
set @ctr = @ctr + 1
set @finalquery = replace(replace(@basequery, '<<COLUMNS>>', @codeBlock1), '<<LIST>>', @codeblock2)