Execute the following script in 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;

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