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.