Execute the following SQL Server T-SQL example script in SSMS Query Editor to create salesperson-year crosstab report. Due to PIVOT list hard-wiring, this query requires maintenance on January 2 of each year.

USE AdventureWorks;

-- SQL crosstab report - SQL crosstab query - SQL pivot query

-- SQL cte - Common Table Expression - SQL currency format - dollar format

WITH cteSalesSummary(OrderYear,SalesPerson,SubTotal)

     AS (SELECT Year(OrderDate),

                FirstName + ' ' + LastName,

                SubTotal

         FROM   Sales.SalesOrderHeader sod

                JOIN HumanResources.Employee e

                  ON sod.SalesPersonID = e.EmployeeID

                JOIN Person.Contact c

                  ON e.ContactID = c.ContactID

         WHERE  SalesPersonID IS NOT NULL)

-- !! USE commented out query below to establish PIVOT list

-- SELECT DISTINCT OrderYear FROM cteSalesSummary

SELECT SalesPerson,

       '$' + Convert(VARCHAR,Isnull(pvt.[2001],0),1) AS [2001],

       '$' + Convert(VARCHAR,Isnull(pvt.[2002],0),1) AS [2002],

       '$' + Convert(VARCHAR,Isnull(pvt.[2003],0),1) AS [2003],

       '$' + Convert(VARCHAR,Isnull(pvt.[2004],0),1) AS [2004]

FROM   cteSalesSummary

       PIVOT

       (Sum(SubTotal)

        FOR OrderYear IN ( [2001],[2002],[2003],[2004] ) ) AS pvt

GO

/* Partial results

SalesPerson       2001              2002              2003                    2004

Amy Alberts       $0.00             $103,718.63       $670,003.71       $118,261.31

David Campbell    $608,545.96       $1,442,147.36     $1,685,690.99     $815,513.35

Garrett Vargas    $568,971.04       $1,452,266.56     $1,675,681.81     $683,794.84

Jae Pak           $0.00             $3,046,514.65     $5,074,216.43     $2,181,995.61

Jillian Carson    $1,499,400.39     $4,605,107.89     $4,402,104.99     $1,651,313.92

José Saraiva      $1,248,852.65     $2,208,660.11     $2,261,596.99     $1,491,540.76

Linda Mitchell    $1,374,860.13     $3,983,247.31     $4,995,825.74     $2,293,796.65

*/