How to get Yearly data in Sql Server

This article demonstrate how to get yearly data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different yearly aggregated sales data formats.

Yearly data in Sql Server

Let us create a Sales table and insert 1000 Sample Sales Records With Random sales date in the past 5 years using the below script.

CREATE DATABASE SqlHintsYearlyData
GO
USE SqlHintsYearlyData
GO
--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With 
--Random past 0 to 5 years as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(YEAR, - ROUND(5 * RAND(), 0),GETDATE()))
GO 1000

Demo 1: Getting Yearly Data by using Group By

SELECT YEAR(SalesDate) [Year], Count(1) [Sales Count]   
FROM #Sales
GROUP BY YEAR(SalesDate)
ORDER BY 1

RESULT:
Yearly data in Sql Server by Group By

Demo 2: Getting Yearly data using Static PIVOT

[ALSO READ] PIVOT and UNPIVOT in Sql Server

SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
      Count(1)  [Sales Count]   
FROM #Sales
GROUP BY YEAR(SalesDate)) AS YearlySalesData
PIVOT( SUM([Sales Count])   
    FOR [Year] IN ([2010],[2011],[2012],
 [2013],[2014],[2015])) AS YearPivot

RESULT:
Yearly data in Sql Server by Pivot

Demo 3: Getting Yearly data using Dynamic PIVOT

[ALSO READ] Dynamic PIVOT in Sql Server

Problem with Demo 2 approach is , it is using the static PIVOT to get the data. Which requires the developer to specify all the years as the pivot columns manually. We can re-write the Demo 2 examples by using Dynamic pivot approach as below which doesn’t require the developer to manually specify the years column.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT YEAR(SalesDate) [Year],  Count(1)  [Sales Count]   
	INTO #PivotSalesData
FROM #Sales
GROUP BY YEAR(SalesDate) 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Year])
FROM (SELECT DISTINCT [Year] FROM #PivotSalesData) AS Years
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT ' + @ColumnName + '
    FROM #PivotSalesData
    PIVOT(SUM( [Sales Count]   ) 
          FOR [Year] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:
Yearly data in Sql Server by Pivot

[ALSO READ]:
How to get Quarterly Data in Sql Server
How to get Monthly Data in Sql Server
How to get Daily data in Sql Server
How to get Hourly data in Sql Server

5 thoughts on “How to get Yearly data in Sql Server

  1. Hi Team,
    I tried to generate Dynamic Pivot table for Yearly Data. I am getting error saying ” Incorrect Syntax Error”. Below is the Script and Can you please help me in solving the same.

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)

    SELECT YEAR([Month]) [Year], Count(1) [Average_Cycle_Time_HH_MM]
    INTO dypv
    FROM [dbo].[Test_Cycle_Time]
    GROUP BY YEAR([Month])
    –Get distinct values of the PIVOT Column
    SELECT @ColumnName= ISNULL(@ColumnName + ‘,’,”)
    + QUOTENAME([Year])
    FROM (SELECT DISTINCT [Year] FROM dypv) AS Years
    –Prepare the PIVOT query using the dynamic
    SET @DynamicPivotQuery =
    N’SELECT ‘ + @ColumnName + ‘
    FROM dypv
    PIVOT( [Average_Cycle_Time_HH_MM] )
    FOR [Year] IN (‘ + @ColumnName + ‘)) AS PVTTable’
    print @DynamicPivotQuery
    –Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery

    Looking forward for your reply.
    Thanks in Advance.

Leave a Reply

Your email address will not be published.