A Pivot Table is a query that allows analysis of data, using the content of the data to group & summarize the results. In MS-Access, pivot tables are referred to as crosstab queries.

For example, let's say you have a table with data that lists payment date, amount paid, and type of tender (cash / check / charge) and we want to view the payment totals by date, by tender type.

Ex 1: This will create the sample table in Access:


CREATE TABLE tblData (
  PayDate DATETIME,
  Amount CURRENCY,
  Tender TEXT(10)
)



Ex 2: This will create the sample table in SQL Server:


CREATE TABLE tblData(
  PayDate smalldatetime NOT NULL,
  Amount money NOT NULL,
  Tender varchar(50) NOT NULL
) ON PRIMARY


In MS-Access, the crosstab query might look like this:


TRANSFORM Sum(tblData.Amount) AS SumOfAmount
SELECT tblData.PayDate
FROM tblData
GROUP BY tblData.PayDate
ORDER BY tblData.PayDate
PIVOT tblData.Tender;



...notice that you do not need to specify the column names; Jet will examine the contents of the Tender field and supply the appropriate names. You could, however specify the column names, and that might look like this:


TRANSFORM Sum(tblData.Amount) AS SumOfAmount
SELECT tblData.PayDate
FROM tblData
GROUP BY tblData.PayDate
ORDER BY tblData.PayDate
PIVOT tblData.Tender In ("Cash","Check","Charge");



SQL Server 2000 does not have any built-in Pivot functionality; 2005 does have some features but not on the scale that Jet has. Normally you would create a pivot table in T-SQL using something like this:


SELECT
  PayDate,
  SUM(CASE Tender
    WHEN 'Cash' THEN Amount
    ELSE 0
  END) AS ByCash,
  SUM(CASE Tender
    WHEN 'Check' THEN Amount
    ELSE 0
  END) AS ByCheck,
  SUM(CASE Tender
    WHEN 'Charge' THEN Amount
    ELSE 0
  END) AS ByCharge
FROM
  tblData
GROUP BY
  PayDate
ORDER BY
  PayDate



..notice that each possible Tender value needs to be explicity defined; any other Tender values that might occur (e.g. 'Coupon') would not show in the results.

There are techniques in T-SQL to discover the necessary column names, but that's for another day.