Technical Article

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 =
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])
NextBack.Month, NextBack.DBName, NextBack.[BackupSizeMB]
into #dump
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
@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)
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)



5 (1)




5 (1)