How to Create a Pivot Table in SQL

Pivot tables are powerful tools in SQL (Structured Query Language) that allow you to create a table or cross-tab similar to an Excel spreadsheet from the contents of a database. It doesn’t matter what the database is--SQL Server, MS Access, Paradox, etc.--as long as it supports SQL.

You need to know the name of the table you want to create the pivot table with and the names of the fields you want in the pivot table. You will also need to know how to create a query in your particular database.

Instructions

1

Open the table you want to create the pivot table in and look at the data and fields. Determine which fields you want in the pivot table and what calculation you want to perform. Perhaps it is a table that tracks sales commissions at various agencies that represent your work and you want to view the monthly sales by each agency. For this example, create a row for each agency and a column for each month. Use the SQL format function to format the data so it reads clearly in the table.

2

Open the SQL query window in your database software and enter the basic SQL statement to create pivot tables. You can use the standard syntax of "tablename.fieldname," but since this will be on a single table, you can just use the field names. For clarity, keywords will be in uppercase and table and field names will be in [square brackets]. You can use this method for writing the real query.

3

Type in the basic query to get started:

TRANSFORM function([field for calculation]) as variablename1
SELECT [field for row], function([field for calculation]) as variablename2
from [tablename]
GROUP by [field for row]
PIVOT [field for columns]

"Function" is the calculation you want to perform for the body of the table. In this case, it will be "sum," as you want a total of commissions. You could use "average" or any other function that gives you the results you need.

4

Enter the query as shown above, substituting your field and table names, and see the results. It may not be what you are looking for but it will give you an idea of how it works, and then you can fine-tune it using standard SQL functions and syntax. In this case, we want to format the commissions in currency format, and we want to format the salesdate field to display months in the three-letter abbreviation. Again, using the example cited above, the complete query would look like this:

TRANSFORM format(Sum([Sales 2010].Commission),"currency") AS SumOfCommission
SELECT [Sales 2010].Agency, format(Sum([Sales 2010].Commission),"currency") AS [Total Of Commission]
FROM [Sales 2010]
GROUP BY [Sales 2010].Agency
PIVOT Format([SalesDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");