Microsoft Access转换表 [英] Microsoft Access Transform Table

问题描述

亲爱的....



帮帮我... ...



我有桌子：

dear all....

Help me plzz...

I have Table:

----------------------------
Name    C1    C2    C3    C4
----------------------------
t1001   10    20    30    40
t1002   1      2     3    4
t1003   11    12    13    14
t1004   5     10    15    20
----------------------------

i来得到这样的输出：

i wan to get output like this :

-----------------------------------------
Name    t1001    t1002    t1003    t1004
-----------------------------------------
C1      10       1        11       5
C2      20       2        12       10
C3      30       3        13       15
C4      40       4        14       20
-----------------------------------------

我真希望有人能帮到我这里！

提前谢谢：）

I really hope some one can help me out here!
Thanks in advance :)

推荐答案

通常使用PIVOT或TRANSFORM操作来旋转结果集。 Access具有TRANSFORM语句，可以在这种情况下使用。请参阅 TRANSFORM语句（Microsoft Access SQL） [ ^ ]



但是，如果这与报告或类似的事情有关，我建议在报告工具中进行所有格式化，而不是在SQL中。这样可以省去很多麻烦。



话虽如此，如果行数是静态的，你还可以考虑类似



如果原始查询是

Rotating the result set is normally done using PIVOT or TRANSFORM operation. Access has a TRANSFORM statement which could be used in this case. See TRANSFORM Statement (Microsoft Access SQL)[^]

However, if this has to do with something reporting or similar, I would advise to do all the formatting in reporting tool, not in the SQL. This will save a lot of headache.

Having that said, if the amount of rows is static, you can also consider something like

If the original query is

SELECT Name, C1, C2, C3, C4
FROM MyTable
WHERE Name IN ('t1001','t1002','t1003','t1004')

你可以像这样旋转：

you could 'rotate' it like this:

SELECT 'C1' AS Name,
       t1.c1 as 't1001',
       t2.c1 as 't1002',
       t3.c1 as 't1003',
       t4.c1 as 't1004'
FROM MyTable t1,
     MyTable t2,
     MyTable t3,
     MyTable t4,
WHERE t1.Name = 't1001'
AND   t2.Name = 't1002'
AND   t3.Name = 't1003'
AND   t4.Name = 't1004'
UNION ALL
SELECT 'C2' AS Name,
       t1.c2 as 't1001',
       t2.c2 as 't1002',
       t3.c2 as 't1003',
       t4.c2 as 't1004'
FROM MyTable t1,
     MyTable t2,
     MyTable t3,
     MyTable t4,
WHERE t1.Name = 't1001'
AND   t2.Name = 't1002'
AND   t3.Name = 't1003'
AND   t4.Name = 't1004'
UNION ALL
SELECT 'C3' AS Name,
       t1.c3 as 't1001',
       t2.c3 as 't1002',
       t3.c3 as 't1003',
       t4.c3 as 't1004'
FROM MyTable t1,
     MyTable t2,
     MyTable t3,
     MyTable t4,
WHERE t1.Name = 't1001'
AND   t2.Name = 't1002'
AND   t3.Name = 't1003'
AND   t4.Name = 't1004' and so on

感谢您给定的解决方案[解决方案1 ​​]。



此解决方案无法解决我的问题，但请给我一个解决问题的想法。



我需要的是[ ColName ]和[ Value ]从特定列[名称]，我为每个列（Cx）创建一个新查询，并使用 UNION ALL 组合它们



创建新查询>> MyQuery

Thanks for a given solution [Solution 1].

This solution does not answer my problem , but give me idea to solve my problem .

Throughout I need is [ColName] and [Value] from a specific column [Name] , I create a new query for each column (Cx) and combine them using the "UNION ALL"

Create new Query >> MyQuery

SELECT [Name], "C1" AS ColName, [C1] as [Value] FROM MyTable
UNION ALL
SELECt [Name], "C2" AS ColName, [C2] as [Value] FROM MyTable
UNION ALL
SELECt [Name], "C3" AS ColName, [C3] as [Value] FROM MyTable
UNION ALL 
SELECt [Name], "C4" AS ColName, [C4] as [Value] FROM MyTable;

查询输出：

Query Output :

-----------------------
Name	ColName	Value
-----------------------
t1001   C1		10
t1002	C1		1
t1003	C1		11
t1004	C1		5
t1001   C2		20
t1002	C2		2
t1003	C2		12
t1004	C2		10
t1001   C3		30
t1002	C3		3
t1003	C3		13
t1004	C3		15
...
-----------------------

然后我可以通过我创建的应用程序得到我需要的东西。



来自应用程序的reQuery：

then i can get what i need through the application that i created.

reQuery from Application :

sSQL = "SELECT MyQuery.[ColName], SUM(MyQuery.[Value]) AS SumValue " & _
       " FROM MyQuery WHERE(MyQuery.[Name] = '" & Param1 & "') " & _
       " GROUP BY Left(MyQuery.[Name], MyQuery.[ColName];"

输出：Param1 = t1001

Output : Param1 = t1001

---------------
ColName	Value
---------------
C1	10
C2	20
C3	30
C4	40
---------------

谢谢&对不起我的英文.. 。:)

thank's & sorry for my english... :)

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