CODEWITHSUNDEEP
sqlasp.netsql-servert-sqlpivot
Prhem
Prhem

Reputation: 117

Average Column-wise by using T-sql and Pivot

I have developed Financial Year Data Fiscal year wise Till now i have achieved! But i couldn't get column-wise average

My table Definition

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

My Query

    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

Kindly please do help to acheive the answer

My output Now :

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

Expected Output

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

Kindly Help !

Upvotes: 2

Views: 314

Answers (1)

Stephan
Stephan

Reputation: 6018

I cleaned up your code a bit. I simply use a UNION ALL and AVG your columns. Note: I use 13 as the month order for the 'Average' row so when it's ordered, it goes at the end. If you need anything else let me know.

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

Upvotes: 1

Related Questions