Example using pivot in SQL Server

-- Create a test table
CREATE TABLE [dbo].[AnnualSales]
 (         
       [CustomerID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,         
       [Gender] [char](1) NULL,               
       [City] [varchar](25) NULL,
       [Education] [varchar](25) NULL,               
       [AnnualPurchases] [money] NULL
 ) 
 
 -- Insert some test data
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'University', 6223) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'High School', 4233) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'University', 6560) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','Chicago', 'University', 5001) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','New York', 'University',7034) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Chicago', 'University',5345) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'High School',790) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'None', 240) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','Seattle', 'University', 4300) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'None', 232)

  -- Use Pivot
  -- Columns that get displayed
  SELECT City, 
         M, 
         F
    FROM (
           -- We are summing AnnualPurchases, our pivot defines the columns by gender so the rows are cities
           SELECT Gender, 
                  City, 
                  AnnualPurchases
             FROM annualSales) ansales
            PIVOT (avg(AnnualPurchases) 
              FOR Gender IN (M, F)
          ) 
      AS pvt
ORDER BY City

I wanted to show an example of the Pivot operator in SQL Server. So I implemented an example that I saw in an O'Reilly excel pivot table tutorial. ** page 2 of this tutorial has an error, the average male purchase for New York should be 3566.