Pivot a table in SQL Server that contains yearly data

Consider the following table recording expenses:

CREATE TABLE dbo.Expenses
(
    ExpenseId INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
    ExpenseType VARCHAR(20) NOT NULL,
    Amount DECIMAL(6,2) NOT NULL,
    ExpenseDate DATE NOT NULL
)   

We populate the table with the following data:

ExpenseId ExpenseType Amount ExpenseDate
1 Rent 340.50 2017-01-01
2 Food 23.50 2017-01-03
3 Food 27.50 2017-04-08
4 Gasoline 50.30 2017-01-05
5 Food 27.50 2016-01-08
6 Rent 340.50 2016-01-01
7 Food 85.00 2015-07-17
8 Gasoline 48.40 2015-12-15
9 Furniture 540.00 2015-09-22
10 Rent 335.00 2015-01-01

Our aim is to get a pivoted view of this table:

ExpenseType 2015 2016 2017
Food 540.00 0.00 0.00
Furniture 85.00 27.50 51.00
Gasoline 48.40 0.00 50.30
Rent 335.00 340.50 340.50

If the list of years is static, in other words if we know before-hand the list of years to be taken into account, then we can make use of the PIVOT relational operator:

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Here, we want to turn the unique year values of column ExpenseDate:

  • 2015
  • 2016
  • 2017

into multiple columns and perform aggregation on column Amount. This operation will be performed for each value of column ExpenseType.

To get the year values of ExpenseDate column we use SQL Server's YEAR built-in function. So, the table-valued expression that PIVOT will operate on looks like this:

SELECT ExpenseType, YEAR(ExpenseDate) AS ExpenseYear, Amount
FROM Expenses

PIVOT is performed for column ExpenseYear, whereas Amount column is the one to be aggregated using SUM aggregate function:

PIVOT
( 
   SUM(Amount)
   FOR ExpenseYear IN ([2015], [2016], [2017])
) AS PivotTable

Hence, the query we want looks like this:

SELECT ExpenseType, 
       COALESCE([2015], 0) AS [2015], 
       COALESCE([2016], 0) AS [2016], 
       COALESCE([2017], 0) AS [2017]
FROM (SELECT ExpenseType, YEAR(ExpenseDate) AS ExpenseYear, Amount
      FROM Expenses) AS SourceTable
PIVOT
( 
   SUM(Amount)
   FOR ExpenseYear IN ([2015], [2016], [2017])
) AS PivotTable

Note the usage of COALESCE around each year column: in case there are no values for a specific Expense Type - ExpenseDate pair, then 0 is produced in the output.

Click here for a live demo.

No comments:

Post a Comment

Featured Post

Store the result of a select query into an array variable

Popular Posts