交叉表查询:使用固定值创建其他列 [英] Crosstab Queries: using fixed values to create additional columns

问题描述

有没有人阅读有关使用TRANSFORM语句创建交叉表查询的MSDN页面?

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

它包含以下断言，代码示例不支持:

您还可以包含不存在数据的固定值以创建其他列.

是的，我想创建一个数据透视表，其中包含来自现有列表的固定的有序列标题集.这是一个简化的SQL查询:

    将SUM(tblData.Losses)转换为TotalLosses
    选择tblData.LossType
    来自tblData
    按tblData.Region分组
    PIVOT tblData.Year; 

我想添加不在表中的区域名称，并且我希望区域以特定的顺序出现.是的，我可以创建一个区域列表并将其左连接:但是，这也不会强加任意顺序-交叉表查询始终按字母顺序从左到右对列进行排序.

我可能只想添加不存在数据的任意固定值.

这是MSDN的信息:

语法

TRANSFORM 聚集函数 selectstatement PIVOT pivotfield [IN( value1 [， value2 [，…]])] p

TRANSFORM语句包含以下部分:

• agggfunction :对所选数据进行操作的SQL聚合函数.
• selectstatement :一条SELECT语句.
• pivotfield :要用于在查询结果集中创建列标题的字段或表达式.
• value1 ， value2 :用于创建列标题的固定值.

...剩下的只是从教科书数据创建普通香草数据透视表的绒毛.

所以，我的问题是:

有人真正使用过固定值来创建列标题吗?

SQL示例将很有用.

---

这是有关Microsoft Access SQL的已发布语法的问题.

谢谢您不要问我为什么要这样做，它给出了冗长的SQL示例，回答了以下问题:是否存在通过对所有内容进行硬编码来执行TRANSFORM语句的ANSI SQL ?"或指出在大型机上的Postgres中这样做会更容易.

解决方案

是的，我能够在Access中使用一组固定的四个值来做到这一点.

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'); 

我的结果是:

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

从这个结果中，我可以确定一些事情:

• 我的来源QComb列中确实存在值"2_HCA_PT"和"3_HCA_PT".
• 我的来源QComb列中的值'1_HCA_PT'和'4_HCA_PT'不存在.
• 在我的来源中，QComb列中还有其他值未在PIVOT列标题中表示.我知道是因为31.38>(0.30 + 7.80).

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.

解决方案

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

这篇关于交叉表查询:使用固定值创建其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！