vba 我想在 ms-access 中将行转换为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40593728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I want to transpose rows into columns in ms-access
提问by rvg6991
I need to transpose rows into columns in MS Access database, VBA, SQL both the codes are welcome.
我需要将行转换为 MS Access 数据库、VBA、SQL 中的列，这两种代码都是受欢迎的。
Table
桌子
| Name | Ticker | ID | Innovation | Quality | Year |
| XYZ | PQR | 11 | 1 | 1 | 2009 |
| XYZ | PQR | 11 | 0 | 1 | 2010 |
| XYZ | PQR | 11 | 1 | 0 | 2011 |
| XYZ | PQR | 11 | 1 | 1 | 2012 |
Desired Table
所需表
| Year | 2009 | 2010 | 2011 | 2012 |
| Name | XYZ | XYZ | XYZ | XYZ |
| Ticker | PQR | PQR | PQR | PQR |
| ID | 11 | 11 | 11 | 11 |
| Innovation | 1 | 0 | 1 | 1 |
| Quality | 1 | 1 | 0 | 1 |
As you can see from the desired table, I am trying to have the Year row as Column and list all the columns apart from Year as my rows. I have tried using Tranform and Pivot function in MS Access but it only Pivots one variable. Let me know your thoughts on it.
正如您从所需的表中看到的，我试图将 Year 行作为 Column 并将除 Year 之外的所有列列为我的行。我曾尝试在 MS Access 中使用 Transform 和 Pivot 函数，但它只能旋转一个变量。让我知道你对此的看法。
The below code failed in transposing all the variables.
以下代码未能转置所有变量。
TRANSFORM Max([Quality])
SELECT Ticker
FROM Table
Where Ticker = "XYZ"
GROUP BY Ticker
PIVOT Year;
Also, if possible I want to publish it as PDF document.
另外，如果可能的话，我想将其发布为 PDF 文档。
Thanks in advance, RVG
提前致谢，RVG
回答by user6788933
Access TRANSFORM is not really intuitive and easy to use and I do not think you can use it that way. Each result row is supposed to be an aggregate of your table. I know of no way to get the previous field names into a new column.
Access TRANSFORM 并不是非常直观和易于使用，我认为您不能那样使用它。每个结果行都应该是表的聚合。我不知道如何将以前的字段名称放入新列中。
See a working example: TRANSFORM and PIVOT in Access 2013 SQL
查看一个工作示例： Access 2013 SQL 中的 TRANSFORM 和 PIVOT
What you really seem to want is just a new presentation to the existing data.
您真正想要的只是对现有数据的新演示。
Excel might help.
Excel 可能会有所帮助。
I have never exported pdf from Access but from Excel is easy. Here is an example:
我从未从 Access 导出 pdf，但从 Excel 导出很容易。下面是一个例子：
Sub ExportPdf(path As String, Optional openAfter As Boolean)
''calculate best range for print area
Dim lastCol As Integer
Dim firstRow As Integer
Dim lastRow As Integer
lastCol = pt.TableRange2.Columns(pt.TableRange2.Columns.Count).Column
firstRow = pt.TableRange2.Rows(1).Row
lastRow = ms.Cells(pt.TableRange2.Rows.Count * 3, 1).End(xlUp).Row
Worksheets(ContextSheet).PageSetup.PrintArea = Range(Cells(firstRow, 1), Cells(lastRow, lastCol)).Address
Worksheets(ContextSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
path & "\Area " & getPivotTablePageFilters(getPivotTable()) & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=openAfter
End Sub
回答by Don George
This is a vb script that takes the data from TableSource and transposes it into TableTranspose. The 2nd table has to be set up with a column named FName to take the field names, and columns for each year in the source table.
这是一个 vb 脚本，它从 TableSource 中获取数据并将其转置为 TableTranspose。第二个表必须设置一个名为 FName 的列以获取字段名称，以及源表中每年的列。
Function Transpose()
Set db = CurrentDb()
db.Execute ("delete * from TableTranspose")
Set RS = db.OpenRecordset("TableSource")
Set YrList = db.OpenRecordset("select distinct [Yr] from [TableSource] Group by [Yr]")
For Each F In RS.Fields
FN = F.Name
INS = "Insert Into TableTranspose (FName"
SQL = "Select '" & FN & "'"
YrList.MoveFirst
Do While Not YrList.EOF
YR = YrList.Fields("YR").Value
INS = INS & ",[" & YR & "]"
SQL = SQL & ",max(iif(YR=" & YR & ",[" & FN & "])) AS [" & YR & "]"
YrList.MoveNext
Loop
SQL = SQL & " From TableSource"
db.Execute (INS & ") " & SQL)
Next F
MsgBox ("Done")
End Function
This works by processing one field at a time to match the layout of the desired output, and looping through each year of TableSource to find the data to make up the row in TableTranspose. It shouldn't matter how many fields there are or what they are named.
其工作原理是一次处理一个字段以匹配所需输出的布局，并循环遍历 TableSource 的每一年以查找构成 TableTranspose 行的数据。有多少字段或它们的名称无关紧要。
It will create a row in the output for the Year, which will be redundant - you can delete it, or add logic to skip that field if necessary.
它将在 Year 的输出中创建一行，这将是多余的 - 您可以删除它，或者在必要时添加逻辑以跳过该字段。
This seems to work fine with the 4 years of data in your sample, and should extend OK to more years. It's possible that you will hit a limit on SQL command length if there are too many years in the data, but I think not.
这似乎适用于您样本中 4 年的数据，并且应该可以扩展到更多年。如果数据中的年份过多，您可能会遇到 SQL 命令长度的限制，但我认为不会。
If you are filtering the records from TableSource, you can add the WHERE clause on the line just from the db.execute near the bottom.
如果要从 TableSource 中过滤记录，则可以在靠近底部的 db.execute 行中添加 WHERE 子句。
回答by Don George
Is the data always the same set of field names and years? If so, you might be able to use a UNION query, something like:
数据是否总是一组相同的字段名称和年份？如果是这样，您可以使用 UNION 查询，例如：
Select "Name" as [FName], max(iif(year="2009",name))as [2009], max(iif(year="2010"
,name)) as [2010], max(iif(year="2011",name)) as [2011], max(iif(year="2012", name)) as [2012] from Table group by FName
Union all Select "Ticker", max(iif(year="2009",ticker)), max(iif(year="2010"
,ticker)), max(iif(year="2011",ticker)), max(iif(year=-"2012",ticker)) from Table group by FName
Union all Select "ID", max(iif(year="2009",id)), max(iif(year="2010"
,id)), max(iif(year="2011",is)), max(iif(year="2012",id)) from Table group by FName
Union all Select "Innovation", max(iif(year="2009",innovation)), max(iif(year="2010"
,innovation)), max(iif(year="2011",innovation)), max(iif(year=-"2012",innovation)) from Table group by FName
Union all Select "Quality", max(iif(year="2009",quality)), max(iif(year="2010"
,quality)), max(iif(year="2011",quality)), max(iif(year=-"2012",quality)) from Table group by FName
