J'ai développé datatables de l'année financière sagement sage jusqu'à maintenant j'ai accompli! Mais je ne pouvais pas get la moyenne par colonne
Ma définition de table
CREATE TABLE [dbo].[tblact] ( [Id] INT NOT NULL, [years] NVARCHAR (MAX) NULL, [months] NVARCHAR (MAX) NULL, [expenses] DECIMAL (18, 2) NULL, [closingbal] DECIMAL (18, 2) NULL, [monthorder] INT NULL
Ma requête
CREATE PROCEDURE fiscalyear AS DECLARE @qstr AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) declare @sql nvarchar(max) SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(years)FROM (SELECT DISTINCT years FROM tblact) AS years; SET @qstr ='SELECT months, ' + @ColumnName + ',total,average 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 order by monthorder '; EXEC sp_executesql @qstr
S'il vous plaît veuillez faire de l'aide pour get la réponse
Ma sortie maintenant:
Months | 2009-2010 | 2010 - 2011 | 2012-2013 | Total | Average -------------------------------------------------------------- April | 2000 | 3000 | 4000 | 9000 | 3000 MAY | 2000 | 3000 | 4000 | 9000 | 3000 --------------------------------------------------------------
Production attendue
Months | 2009-2010 | 2010 - 2011 | 2012-2013 | Total | Average -------------------------------------------------------------- April | 2000 | 3000 | 4000 | 9000 | 3000 MAY | 2000 | 3000 | 4000 | 9000 | 3000 -------------------------------------------------------------- Average| 2000 | 3000 | 4000 | 9000 | 3000
Aide aimablement !
J'ai nettoyé ton code un peu. J'utilise simplement UNION ALL et AVG vos colonnes. Note: J'utilise 13 comme l'ordre du mois pour la ligne 'Moyenne' alors quand il est commandé, il va à la fin. Si vous avez besoin d'autre chose, faites-moi savoir.
CREATE PROCEDURE fiscalyear AS DECLARE @qstr NVARCHAR(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 CTE AS ( 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_Average AS ( SELECT months, ' + @ColumnName +',total,average,monthorder FROM CTE UNION ALL SELECT ''Average'',' + @AvgColumnName + ',AVG(total),AVG(average),13 FROM CTE ) SELECT months,' + @ColumnName + ',total,average FROM CTE_Average ORDER BY monthOrder' EXEC sp_executesql @qstr