Crosstab Queries: using fixed values to create additional columns

Question

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.

Answer 1

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).

Answer 2

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.

Answer 3

Basically, if you're able to get it to return results, the "IN (value1, value2)" bit is just you specifying which pivoted columns you wish to return in your result set.

So for example, if without "IN (value1, value2)" your table looks like this:

id	1	2	3	4
1	a	b	c	d

Adding "IN ('2', '4')" Would return:

id	2	4
1	b	d