Get Quarterly or Annual Sales with SQL Server PIVOT

A customer of mine handles transactions in their database for jewelry purchases, and today I was asked 'is there a way to know how many sales we've done each quarter, or year?'  Why yes, there is!  This is a very quick post showing a couple different ways to summarize sales or transactions, using SQL Server's PIVOT operator.  

This first example is 'static', where I have to tell SQL which values I am looking for.  In this case, the quarters for which I want the sale counts:

     SELECT

         [Year],

         [1] Qtr1,

         [2] Qtr2,

         [3] Qtr3,

         [4] Qtr4

     FROM (

           SELECT YEAR([PostingDate]) [Year],

           DATEPART(QUARTER, [PostingDate]) [Quarter]

           FROM dbo.YourTable

        ) SourceTable

     PIVOT(COUNT([Quarter])

     FOR [Quarter] IN ([1], [2], [3], [4])) PivotTable

As you can see, our rows come back as columns for each of the quarters in my query.  It's 'static' because this requires you to manually input the values you wish to query.  All very nice, but what if you don't know exactly what's in the table?

This next example does not require you to know, or input your values ahead of time.  In this case, the annual transactions, but I don't know exactly which years of data are in the table.  This is a 'dynamic' pivot:

     DECLARE

          @pivotquery NVARCHAR(MAX),

          @column NVARCHAR(MAX)

     SELECT YEAR([Posting Date]) [Year], COUNT(1) [Transaction Count] 

     INTO #Transactions

     FROM dbo.YourTable WITH (NOLOCK) ---- NOLOCK maybe necessary 

     GROUP BY YEAR([Posting Date])

     -- distinct values for pivot colunn

     SELECT @column = ISNULL(@column + ',','') + QUOTENAME([Year])

     FROM (SELECT DISTINCT [Year] FROM #Transactions) Years

     -- now collect the @columns via dynamic sql

     SET @pivotquery = 'SELECT ' + @column + '

                        FROM #transactions

                        PIVOT(SUM( [Transaction Count]   )

                        FOR [Year] IN (' + @column + ')) AS PVTTable'

     -- execute your query

     EXEC sp_executesql @pivotquery

In many cases, I think that 2nd approach may be more useful, because you don't have to know your queried values ahead of time.

Try it out yourself and let me know what you think!  I believe you should also take a look a this for a little more information regarding the use of PIVOT:

       https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx