构建复杂的交叉表 [英] Building a Complex Crosstab
问题描述
我从公司的Sybase数据库中获取提取/报告。这是一个非常大的交易数据库，非常少（或者很慢和
复杂的）报告/查询，所以相反我有一个包罗万象的查询
运行，每晚都放入一个文件夹，然后通过Access执行我的数据
分析/挖掘。
现在这里是我的问题
DATA（存储为Trait）不存储在每个
特征的单独字段中..这里是一个例子
Identifier Trait_Name Trait_Value
12345 SeedNumber 147
12345 HarvestDate 4/15/2005
12345状态收获
等
每个标识符可能有很多行...因为许多行
是与每个标识符相关联的唯一特征和特征值。
当然Trait_Value字段是字符。
现在我想要做的是构建一个交叉表，以便数据看起来像
喜欢这个..每个标识符一行，每个Trait_Name获取一个字段名称
并将trait_values转换为数字，日期或字符
符合类型他们是数据。对于我想要使用的每个特性，我将有一个单独的字段
。
标识符SeedNumber HarvestDate状态
12345 147 4/15/2005收获
12346
12347 23 5/20/2001收获
12348 Killed
这就是我到目前为止在Access2003，QBE窗口中的
，我建立了这个
标识符种子：Iff（[Trait_Name] = SeedNumber，[Trait_Value]，"等
Group by First
这是正确的方法吗？我怎样才能将
信息转换为正确的格式（日期，数字）....我假设使用
format（）？
也许有一种方法可以更容易（并且有力？）在SQL中执行此操作？
如果是这样，我现在也必须学习。
谢谢
Jerry
I get extracts/reports from a corporate Sybase database. This is a
very very large transactional database, very little (or slow and
complex) reports/queries, so instead I have an all-inclusive query
run, dropped into a folder each night, and then do my data
analysis/mining via Access.
Now here''s my problem
The DATA (stored as a Trait) is not stored in separate fields for each
trait .. here''s an example
Identifier Trait_Name Trait_Value
12345 SeedNumber 147
12345 HarvestDate 4/15/2005
12345 Status Harvested
etc
There may be many rows for each identifier... as many rows as there
are unique trait and trait values assocaited with each identifier. Of
course the Trait_Value field is character.
Now what I want to do is to build a crosstab so that the data looks
like this..each identifier a row, each Trait_Name gets a field name
and the trait_values are converted to numeric, date or character to
conform to the type of data they are. I will have a separate field
for each Trait I want to use.
Identifier SeedNumber HarvestDate Status
12345 147 4/15/2005 Harvested
12346
12347 23 5/20/2001 Harvested
12348 Killed
This is what I have so far
in Access2003, QBE window, I built this
Identifier Seed:Iff([Trait_Name]="SeedNumber",[Trait_Value],"") etc
Group by First
So is this the correct way to do this? And how might I convert the
information to the correct format (Date, numeric).... I presume use
format()?
Maybe there''s a way to more easily (and powerfully?) do this in SQL?
If so, I will have to learn that now as well.
Thanks
Jerry
推荐答案
嗨Jerry，
尝试将以下内容粘贴到新查询的SQL部分 - 检查
表名是否正确。
TRANSFORM First（tbl_Traits.Trait_Value ）AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name ;
Hi Jerry,
Try pasting the following into the SQL section of a new query - check
that the table name is correct.
TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;
2006年2月20日14:06:20 -0800，Anton <一个****** @ gmail.com>写道：
On 20 Feb 2006 14:06:20 -0800, "Anton" <an******@gmail.com> wrote:
嗨Jerry，
尝试将以下内容粘贴到新查询的SQL部分 - 检查
表名是否正确。
TRANSFORM First（tbl_Traits.Trait_Value）AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;
Hi Jerry,
Try pasting the following into the SQL section of a new query - check
that the table name is correct.
TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;
我有Access 2000.我在帮助
文件中找不到TRANSFORM关键字。
它是在A2000吗？它是做什么的？
谢谢。
I have Access 2000. I cannot find the TRANSFORM keyword in the help
file.
Is it in A2000? What does it do?
Thanks.
哇安东
作品很棒
我有一些语法错误，我必须解决，但我理解。
有没有办法为字段分配正确的格式，文本日期
到日期，文本到数字等？这些似乎并不简单，因为这个
方法没有明确识别枢轴列。
感谢您的帮助。
我今天学到了一些东西..非常强大
Jerry
2006年2月20日14:06:20 -0800，Anton <一个****** @ gmail.com>写道：
Wow Anton
Works great
I had a few syntax erros I had to work out, but I understand.
Is there a way to assign the proper format to the fields, text dates
to dates, text to numeric, etc? These seems not trivial since this
method does not explicitly identify the pivot columns.
Thanks for your help.
I learned something today with this..very powerful
Jerry
On 20 Feb 2006 14:06:20 -0800, "Anton" <an******@gmail.com> wrote:
嗨Jerry，
尝试将以下内容粘贴到新查询的SQL部分 - 检查
表名是否正确。
TRANSFORM First（tbl_Traits.Trait_Value）AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;
Hi Jerry,
Try pasting the following into the SQL section of a new query - check
that the table name is correct.
TRANSFORM First(tbl_Traits.Trait_Value) AS FirstOfTrait_Value
SELECT tbl_Traits.Identifyer
FROM tbl_Traits
GROUP BY tbl_Traits.Identifyer
PIVOT tbl_Traits.Trait_Name;
这篇关于构建复杂的交叉表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
