如何合并Msacess的行与列中的公共键 [英] How to merge Rows of Msacess with common Key in column

问题描述

我有一张表

I have a Table

QID     Qtext   QType           SeqNo   Header  QuestionGroupedTo
31	sdsad	Group Type	31	sdsd	30
32	dsd	Group Type	32	sd	30
43	ssd	Group Type	40	sd	39
44	ssd2	Group Type	41	ssd2m	39
45      dsds    Group Type      43      asdasd  39

i希望表结构为

i want the table structure to be

QID      QuestionGroupTo
31~32     30  
43~44~45  39

我怎样才能实现这个

How can i achieve this

推荐答案

首先，你必须创建一个模块此代码：

First of all, you must create a module with this code:

Option Compare Database

Public Function Aggregate(value As Integer) As String
    Dim retValue As String
    
    Dim db As Database
        Set db = CurrentDb()
    
    Dim sql As String
    sql = "SELECT * FROM T WHERE QuestionGroupedTo=" & value
    
    Dim rs As Recordset
        Set rs = db.OpenRecordset(sql)
    
    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            If retValue <> "" Then retValue = retValue & "~"
            retValue = retValue & CStr(rs("QID"))
            rs.MoveNext
        Loop
    End If
    
    
    Aggregate = retValue
End Function

这是您创建的聚合函数，用于连接所需的值。



然后你的sql语句可以是这样的：

This is an aggregate function that you create to concatenate the required values.

And then your sql sentence can be like this:

SELECT T.QuestionGroupedTo, Aggregate([QuestionGroupedTo]) AS Expr1
FROM T
GROUP BY T.QuestionGroupedTo, Aggregate([QuestionGroupedTo]);

greetings

greetings

这篇关于如何合并Msacess的行与列中的公共键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！