Is great to know that you're sharing your real life experience. As commented above, the cross tabs approach has been way back before pivot and is more flexible. That doesn't means that you didn't do a very good effort and showed what's needed when using a table variable (even if most people like me won't advocate their use).
I'd like to encourage you to use ORDER BY in your queries even if DISTINCT is used (which needs a sort by itself) to define real order of your columns. I'll also suggest that you use the FOR XML PATH concatenation approach[/url] which allows for a more secure and flexible order of the columns. I've seen that the columns in a pivot get messed up because the ORDER BY was ignored in the concatenation method you used.
This is the approach I usually follow for dynamic pivots.
declare @sql nvarchar(max)
SELECT @sql = 'SELECT TableName ' + CHAR( 13)
+ (SELECT CHAR(9) + ', AVG( CASE WHEN YEAR( CreatedDate) = ' + calyear + ' THEN RecordCount END) AS ' + QUOTENAME(calyear) + CHAR(13)
FROM @TempTable t
CROSS APPLY (SELECT CONVERT( CHAR(4),YEAR( CreatedDate))) y( calyear)
GROUP BY calyear
ORDER BY calyear
FOR XML PATH(''),TYPE).value('.', 'nvarchar(max)')
+ CHAR(9) + ',AVG( RecordCount) AS Total ' + CHAR( 13)
+ 'FROM @TempTable ' + CHAR( 13)
+ 'GROUP BY TableName ' + CHAR( 13)
+ 'ORDER BY TableName; '
exec sp_executesql @sql,N'@TempTable dbo.TableType READONLY', @TempTable;
Luis C.How to post data/code on a forum to get the best help: Option 1 / Option 2
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?