question

This seems like a really poor way to hold this data. By converting the date dimension to a column name element you lose the ability to run any date based analysis of your row counts. Surely that's the point of tracking them? Also, what happens if new *tables* get added? I'd hold this data in a columnar format, then use SQL to present it in the way that you need. Admittedly, this is logically more complicated, but it feels worth it to me. -- setup simplified dummy data create table #row_counts (TableName varchar(20), CheckDate datetime, RowsInTable int) insert #row_counts values('Bob','1 January 2015',100) insert #row_counts values('Dave','1 January 2015',100) insert #row_counts values('Steve','1 January 2015',100) insert #row_counts values('Bob','1 February 2015',150) insert #row_counts values('Dave','1 February 2015',150) insert #row_counts values('Steve','1 February 2015',150) go -- we can add labels dynamically fairly easily: -- use dense_rank to add check numbers select CheckNumber = dense_rank() over (order by CheckDate) , * from #row_counts -- combine this with formatted date to generate labels select CheckNumber = dense_rank() over (order by CheckDate) , CheckLabel = cast(dense_rank() over (order by CheckDate) as varchar) + '_row_count_' + convert(varchar(8),CheckDate,112) , * from #row_counts -- we can then pivot to get columns select TableName , [1_row_count_20150101] , [2_row_count_20150201] from ( select TableName , CheckLabel = cast(dense_rank() over (order by CheckDate) as varchar) + '_row_count_' + convert(varchar(8),CheckDate,112) , RowsInTable from #row_counts ) src pivot ( sum(RowsInTable) for CheckLabel in ( [1_row_count_20150101],[2_row_count_20150201] ) ) pvt -- result: /* TableName 1_row_count_20150101 2_row_count_20150201 -------------------- -------------------- -------------------- Bob 100 150 Dave 100 150 Steve 100 150 */ -- BUT. doing this in reality is a bit trickier -- this is because we don't want to have to explicitly provide the CheckLabel names at runtime -- otherwise a script change would be required every time a new check was run -- to get round this, we can use a dynamic query -- this means that we don't need to make manual changes to scripts when the data changes -- declare a variable to hold the sql statement that we generate declare @sql varchar(2000) -- and another to hold the list of columns (i.e. CheckDates) to extract declare @cols varchar(1000) -- this query extracts the label values that we're interested in -- and converts them to a comma separated string select @cols = stuff((select distinct ',' + quotename(cast(dense_rank() over (order by CheckDate) as varchar) + '_row_count_' + convert(varchar(8),CheckDate,112)) from #row_counts -- you can add a where clause here if you just want a subset for xml path(''), type ).value('.', 'varchar(1000)') ,1,1,'') -- show result print '@cols:' print @cols -- we now use this to generate a pivot query, just like the one we had before -- but this time, we build it as a text string to execute later select @sql = 'select TableName , ' + @cols + ' from ( select TableName , CheckLabel = cast(dense_rank() over (order by CheckDate) as varchar) + ''_row_count_'' + convert(varchar(8),CheckDate,112) , RowsInTable from #row_counts ' -- add a where clause here (within the quoted block) if you added one above -- not absolutely required, but will reduce the set size, so a good idea + ') src pivot ( sum(RowsInTable) for CheckLabel in (' + @cols +') ) pvt' -- show result print '@sql:' print @sql -- execute the query exec (@sql) -- result: /* TableName 1_row_count_20150101 2_row_count_20150201 -------------------- -------------------- -------------------- Bob 100 150 Dave 100 150 Steve 100 150 */ Drop this into a stored procedure with start and end date inputs and you have a nice and simple, flexible way of extracting and presenting the data.