我想将行转置为ms-access中的列 [英] I want to transpose rows into columns in ms-access

问题描述

我需要将行转换为MS Access数据库，VBA，SQL中的列，欢迎使用这两种代码.

I need to transpose rows into columns in MS Access database, VBA, SQL both the codes are welcome.

表

    | 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 | 

所需表

    | 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    |

从所需的表中可以看到，我试图将Year行作为Column，并列出除Year以外的所有列作为我的行. 我试过在MS Access中使用Tranform和Pivot函数，但它仅枢转一个变量.让我知道您对此的想法.

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.

以下代码未能转换所有变量.

The below code failed in transposing all the variables.

    TRANSFORM Max([Quality])
    SELECT Ticker
    FROM Table
    Where Ticker = "XYZ"
    GROUP BY Ticker
    PIVOT Year;

此外，如果可能的话，我想将其发布为PDF文档.

Also, if possible I want to publish it as PDF document.

预先感谢， RVG

推荐答案

这是一个vb脚本，可从TableSource中获取数据并将其转置为TableTranspose.必须在第二个表中设置一个名为FName的列以获取字段名称，并在源表中每年设置一列.

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.

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

这是通过一次处理一个字段以匹配所需输出的布局，并循环遍历TableSource的每一年以查找组成TableTranspose中的行的数据来实现的.有多少个字段或它们的名字都没关系.

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.

它将在Year的输出中创建一行，这将是多余的-您可以将其删除，或者在必要时添加逻辑以跳过该字段.

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.

这似乎可以处理样本中的4年数据，并且可以将有效期延长至更多年.如果数据中的年份太多，则可能会达到SQL命令长度的限制，但我认为不是.

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.

如果要从TableSource过滤记录，则可以仅从底部附近的db.execute行中添加WHERE子句.

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.

这篇关于我想将行转置为ms-access中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！