我整理了一下您的代码。我只是使用UNIOn
ALL和AVG作为您的专栏。注意：我使用13作为“平均”行的月度顺序，因此在订购时，它会在末尾显示。如果您还有其他需要，请告诉我。
CREATE PROCEDURE fiscalyear ASDECLARE @qstrNVARCHAr(MAX), @ColumnName NVARCHAr(MAX), @AvgColumnName NVARCHAr(MAX)SELECt @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(years), @AvgColumnName = COALESCE(@AvgColumnName + ',','') + 'AVG(' + QUOTENAME(years) + ')'FROM tblact GROUP BY years;SET @qstr ='WITH CTEAS( SELECt months, ' + @ColumnName + ',total,average,monthorder FROM (SELECt months, years, expenses,avg(expenses) over(partition by months) average,sum(expenses) over (partition by months) total ,monthorder FROM tblact ) AS p PIVOT(SUM(expenses) FOR years IN (' + @ColumnName + ')) AS PVTTable),CTE_AverageAS(SELECt months, ' + @ColumnName +',total,average,monthorderFROM CTEUNIOn ALLSELECt ''Average'',' + @AvgColumnName + ',AVG(total),AVG(average),13FROM CTE)SELECt months,' + @ColumnName + ',total,averageFROM CTE_AverageORDER BY monthOrder'EXEC sp_executesql @qstr
