Crosstab Queries: using fixed values to create additional columns

1
votes

Has anyone read the MSDN page on using the TRANSFORM statement to create crosstab queries?

http://msdn.microsoft.com/en-us/library/office/bb208956(v=office.12).aspx

It includes the following assertion, unsupported by code samples:

You can also include fixed values for which no data exists to create additional columns.

Yes, I would like to create a pivot table with a fixed ordered set of column headings from a pre-existing list. Here's a simplified SQL query:

    TRANSFORM SUM(tblData.Losses) As TotalLosses
    SELECT tblData.LossType
    FROM tblData
    GROUP BY tblData.Region
    PIVOT tblData.Year;

I would like to add region names that are not in the table and I would like the regions to appear in a specific order. Yes, I can create a region listing table and left-join it: but that won't impose an arbitrary order, either - Crosstab queries always sort the columns left-to-right alphabetically.

And I might just want to add arbitrary fixed values for which no data exists.

Here's MSDN's information:

Syntax

TRANSFORM aggfunction     selectstatement     PIVOT pivotfield [IN (value1[, value2[, …]])]

The TRANSFORM statement has these parts:

  • aggfunction: An SQL aggregate function that operates on the selected data.
  • selectstatement: A SELECT statement.
  • pivotfield: The field or expression you want to use to create column headings in the query's result set.
  • value1,value2: Fixed values used to create column headings.

...And the rest is just fluff for creating a plain-vanilla pivot table from textbook data.

So, my question is:

Has anyone ever actually used fixed values to create column headings?

A sample of your SQL would be useful.

This is a question about the published syntax for Microsoft Access SQL.

Thank you for not asking why I want to do this, giving lengthy SQL examples that answer the question 'Is there ANSI SQL that does what a TRANSFORM statement does, by hardcoding everything?' or pointing out that this would be easier in Postgres on a mainframe.

2
ms-accessms-access-2010

2 Answers

3
votes

Yes, I was able to do this in Access with a fixed set of four values.

TRANSFORM Sum([Shape_Length]/5280) AS MILES
SELECT "ONSHORE" AS Type, Sum(qry_CurYrTrans.Miles) AS [Total Of Miles]
FROM qry_CurYrTrans
GROUP BY "ONSHORE"
PIVOT qry_CurYrTrans.QComb IN ('1_HCA_PT','2_HCA_PT','3_HCA_PT','4_HCA_PT');

My results were:

| Type     | Total Of Miles  | 1_HCA_PT  | 2_HCA_PT  | 3_HCA_PT  | 4_HCA_PT |
| ONSHORE  | 31.38           |           | 0.30      | 7.80      |          |

From this result, I can determine a few things:

  • The values '2_HCA_PT' and '3_HCA_PT' do exist in column QComb from my source.
  • The values '1_HCA_PT' and '4_HCA_PT' do not exist in column QComb from my source.
  • There are additional values in column QComb from my source that aren't represented in the PIVOT column headings. I can tell because 31.38 > (0.30 + 7.80).
1
votes

Try this:

TRANSFORM Sum(tblData.Losses) AS TotalLosses
SELECT tblData.Region, tblData.LossType
FROM tblData
GROUP BY tblData.Region, tblData.LossType
PIVOT tblData.Year In ('2001','2000');

The key being the list of years in the PIVOT statement. You might have to replace the ' with # for datetime datatypes.