在Access中使用查询转置表 [英] Transpose a table using query in Access

问题描述

我有table1，我想要按table1_1所示进行转置，谁能告诉我可用于执行此操作的查询/功能

I have the table1, i want to transpose it as shown in table1_1, can anyone tell me the query/function that can be used to do this

表1

FileName    ObjectName  Column1 Column2 Column3 Column4 Column5

F1           Line1         A       B       C      D       E
F1           Line2         F       G       H      I       J
F1           Line3         K       L       M      N       O


table1_1


FileName    ObjectName       Column    Data
F1            Line1          Column1    A
F1            Line1          Column2    B
F1            Line1          Column3    C
F1            Line1          Column4    D
F1            Line1          Column5    E
F1            Line2          Column1    F
F1            Line2          Column2    G
F1            Line2          Column3    H
F1            Line2          Column4    I
F1            Line2          Column5    J
F1            Line3          Column1    K
F1            Line3          Column2    L
F1            Line3          Column3    M
F1            Line3          Column4    N
F1            Line3          Column5    O

推荐答案

看看交叉表查询与最小"，最大"和最后"一起使用.

Have a look at crosstab queries used with Min, Max and Last.

转置:

TRANSFORM First(Table1_1.Data) AS FirstOfData
SELECT Table1_1.FileName, Table1_1.ObjectName
FROM Table1_1
GROUP BY Table1_1.FileName, Table1_1.ObjectName
PIVOT Table1_1.Column;

归一化:

SELECT FileName, ObjectName, "Column1" As ColName, Column1 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column2" As ColName, Column2 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column3" As ColName, Column3 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column4" As ColName, Column4 As ColData  
FROM Table1 
UNION ALL
SELECT FileName, ObjectName, "Column5" As ColName, Column5 As ColData  
FROM Table1 

这篇关于在Access中使用查询转置表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！