MS Access - 将行值转换为列值 [英] MS Access - Convert rows values into columns values

问题描述

我需要使用 MS Access 转换下表:

进入我如何转换它?我是 ms access 2016 的新手.

我研究了

这是给你的代码:

选项比较数据库选项显式公共函数 Transpose() As StringDim ordersCnt As Integer, sql As String, i As Integer, tempTable As String, f As Variant, myTable As StringDim insSql As String, fieldsSql As String, updateSql As String, updateSql2 As StringDim db As DAO.DatabaseDim rs As DAO.RecordsetDim grp As DAO.RecordsettempTable = "转置"myTable = "MyTable"ordersCnt = GetMaxOrders设置 db = CurrentDb()If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tempTable & "'")) ThenDoCmd.DeleteObject acTable, tempTable万一字段Sql = ""sql = "创建表" &临时表" (项目 CHAR, 资产 CHAR "对于 i = 1 到 ordersCntfieldsSql = fieldsSql &", 订单" &我&"整数"接下来我sql = sql &字段Sql &")"db.Execute (sql)insSql = "插入" &临时表(项目，资产)值("Set grp = db.OpenRecordset("SELECT DISTINCT Project, Asset FROM " & myTable & " GROUP BY Project, Asset")grp.MoveFirst做而不是 grp.EOFsql = "'" &grp(0) &"','" &grp(1) &"')"db.Execute insSql &sqlSet rs = db.OpenRecordset("SELECT * FROM " & myTable & " WHERE Project = '" & grp(0) & "' AND Asset = '" & grp(1) & "'")updateSql = "更新" &临时表"放 "updateSql2 = ""我 = 0rs.先行不做时做 rs.EOF我 = 我 + 1updateSql2 = updateSql2 &订单"&我&=" &rs(3) &，"rs.MoveNext环形updateSql = updateSql &Left(updateSql2, Len(updateSql2) - 1) &" WHERE 项目 = '" &grp(0) &"' AND 资产 = '" &grp(1) &'"db.执行updateSqlgrp.MoveNext环形结束函数公共函数 GetMaxOrders()Dim rst As DAO.RecordsetDim strSQL As StringstrSQL = "SELECT MAX(CountOfOrderNo) FROM (SELECT Count(OrderNo) AS CountOfOrderNo FROM MyTable GROUP BY Project, Asset)"设置 rst = CurrentDb.OpenRecordset(strSQL)GetMaxOrders = rst(0)首先关闭设置 rst = 无结束函数

结果:

享受.

I need to convert following Table using MS Access:

into How do I convert it? I am new to ms access 2016.

I have looked into the solution provided in ACCESS/SQL Combining multiple rows with one column into one row and creating multiple columns. But not able to get the result I want in my case.

解决方案

Here is pure Access solution using code. It may be done more efficient but you will get the idea now. It creates a temporary table Transposed where you find your result. This is MyTable with data

and this is code for you:

Option Compare Database
Option Explicit

Public Function Transpose() As String
    Dim ordersCnt As Integer, sql As String, i As Integer, tempTable As String, f As Variant, myTable As String
    Dim insSql As String, fieldsSql As String, updateSql As String, updateSql2 As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim grp As DAO.Recordset
    tempTable = "Transposed"
    myTable = "MyTable"
    ordersCnt = GetMaxOrders
    Set db = CurrentDb()
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tempTable & "'")) Then
        DoCmd.DeleteObject acTable, tempTable
    End If
    fieldsSql = ""
    sql = "CREATE TABLE " & tempTable & " (Project CHAR, Asset CHAR "
    For i = 1 To ordersCnt
        fieldsSql = fieldsSql & ", Order" & i & " INTEGER"
    Next i
    sql = sql & fieldsSql & ")"
    db.Execute (sql)

    insSql = "INSERT INTO " & tempTable & " (Project, Asset) VALUES ("
    Set grp = db.OpenRecordset("SELECT DISTINCT Project, Asset FROM " & myTable & " GROUP BY Project, Asset")
    grp.MoveFirst

    Do While Not grp.EOF
        sql = "'" & grp(0) & "','" & grp(1) & "')"
        db.Execute insSql & sql
        Set rs = db.OpenRecordset("SELECT * FROM " & myTable & " WHERE Project = '" & grp(0) & "' AND Asset = '" & grp(1) & "'")
        updateSql = "UPDATE " & tempTable & " SET "
        updateSql2 = ""
        i = 0
        rs.MoveFirst
        Do While Not rs.EOF
            i = i + 1
            updateSql2 = updateSql2 & "Order" & i & "= " & rs(3) & ","
            rs.MoveNext
        Loop
        updateSql = updateSql & Left(updateSql2, Len(updateSql2) - 1) & " WHERE Project = '" & grp(0) & "' AND Asset = '" & grp(1) & "'"
        db.Execute updateSql
        grp.MoveNext
    Loop
End Function

Public Function GetMaxOrders()
   Dim rst As DAO.Recordset
   Dim strSQL As String
   strSQL = "SELECT MAX(CountOfOrderNo) FROM (SELECT Count(OrderNo) AS CountOfOrderNo FROM MyTable GROUP BY Project, Asset)  "
   Set rst = CurrentDb.OpenRecordset(strSQL)
   GetMaxOrders = rst(0)
   rst.Close
   Set rst = Nothing
End Function

and the result:

Enjoy.

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