使用T-sql和Pivot按列进行平均

admin

使用T-sql和Pivot按列进行平均

sql

我已经明智地制定了财政年度数据，直到现在为止我已经实现了！但是我无法获得列平均

我的桌子定义

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

我的查询

    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

请帮助您达到答案

我的输出现在：

Months | 2009-2010 | 2010 - 2011 | 2012-2013 | Total | Average
--------------------------------------------------------------
April  | 2000      | 3000        | 4000      | 9000  |  3000
MAY    | 2000      | 3000        | 4000      | 9000  |  3000
--------------------------------------------------------------

预期产量

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

请帮助！

阅读 199

2021-07-01

共1个答案

admin

我整理了一下您的代码。我只是使用UNION
ALL和AVG作为您的专栏。注意：我使用13作为“平均”行的月度顺序，因此在订购时，它会在末尾显示。如果您还有其他需要，请告诉我。

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

2021-07-01