Mechanism To Dynamically Build Pivot Table

kurianchirackel, 2018-10-25 (first published: 2018-10-15)

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)