旋转带有预定义标题的表 [英] Pivoting a table with predefined headers

问题描述

ID | NAME | Description
A    A      Value1
A    A      Value2
B    B      Value1
C    C      Value1
C    C      Value2
C    C      Value3
D    D      Value1
D    D      Value2
D    D      Value3
D    D      Value4
D    D      Value5
D    D      Value6

我有这个输入，我正在尝试将其转换成这个.

I have this input, and i am trying to convert it into this.

ID | NAME | Desc1 | Desc2 | Desc3 | Desc4 | Desc5 | Desc6
A    A      Value1  Value2
B    B      Value1
C    C      Value1  Value2  Value3
D    D      Value1  Value2  Value3  Value4  Value5  Value6

我尝试进行枢轴转换.但不会去任何地方.

I have tried doing pivot and transform. but not going anywhere.

transform max([Description])
select ID, [Description]
from TableName
Group ID
Pivot [Description]

将提供任何帮助.

推荐答案

名称是保留字.不应将保留字用作任何东西的名称.

Name is a reserved word. Should not use reserved words as names for anything.

一种方法使用DCount().该表将需要一个唯一的标识符字段-为此应该使用自动编号.建立查询:

One approach uses DCount(). The table will need a unique identifier field - autonumber should serve for this. Build query:

SELECT Table1.ID, Table1.Name, Table1.Description, Table1.NumID, 
DCount("*","Table1","ID='" & [ID] & "' AND NumID<" & [NumID])+1 AS GrpNum
FROM Table1
ORDER BY Table1.ID, Table1.NumID;

然后在CROSSTAB中使用该查询.

Then use that query in CROSSTAB.

TRANSFORM First(Query3.Description) AS FirstOfDescription
SELECT Query3.ID, Query3.Name
FROM Query3
GROUP BY Query3.ID, Query3.Name
PIVOT Query3.GrpNum;

但是，对于非常大的数据集，域聚合函数在查询中的执行速度会很慢.

However, domain aggregate functions can perform slowly in query for very large dataset.

VBA过程可以将记录写入到透视结构中的临时"表(表是永久性的，数据是临时性的)中.对于大型数据集，此代码的执行速度可能比使用域聚合功能的查询快.

A VBA procedure could write records to a 'temp' table (table is permanent, data is temporary) in the pivoted structure. This code might execute faster than query using domain aggregate function for large datasets.

可以使用自动编号字段将文本文件导入"temp"表，然后运行建议的查询.

Text file could be imported into 'temp' table with autonumber field and then run the suggested queries.

这篇关于旋转带有预定义标题的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！