Comments by Member 14219614 (Top 4 by date)

Thanks for your reply, I see that I had a redundant aggregation in my query and is good to correct that. However, what you've suggested seems to be equivalent to my original code in that still get nulls in the pivoted fields which result in the grand total column having nulls.

To correct this the first line select statement should be SELECT var1, var2, var3, [1], [2], isnull([1],0) + isnull([2],0) AS Total

And this is really the crux of my original question - I don't know how to convert this to dynamic format

Please see the updated dummy data and explanation above the data table

Thanks for your comment, I've added some dummy data which would result in issue described. Sorry I am new to dynamic SQL so question might not be phrased very well