访问sql查询以连接行 [英] access sql query to concatenate rows
本文介绍了访问sql查询以连接行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我以csv格式保存了以下数据,并以访问格式导出了数据
I have following data in csv format which I exported in access format
House Number | Road name | Postcode
1 | alex road | sw2 4r
2 | alex road | sw2 4r
3 | alex road | sw1 2b
4 | alex road | sw2 4r
5 | alex road | sw2 4r
7 | brit road | bw2 4t
4 | brit road | bw2 4t
6 | brit road | bw2 4t
8 | brit road | bw2 4t
10 | brit road | bw2 4t
我需要一个ACCESS SQL查询,该查询可以按路名对所有记录进行分组,总记录可以超过20,000个
I need a ACCESS SQL query which can group all records by road name, total records can be more than 20,000
我需要以以下格式输出
Road Name | House Number | Postcode
--------------------------------------
alex road | 1,2,3,4,5 | sw2 4r,sw1 2b
brit road | 7,4,6,8,10 | bw2 4t
推荐答案
正如有一天所指出的那样,在SO的早期文章中,使用ADO会更容易,其中有
As was pointed out by onedaywhen in an early post on SO, this is easier with ADO, there is an example here
示例查询:
SELECT d.DeptID, d.Department,
ConcatADO("SELECT FName & ' ' & SName, Address FROM Persons
WHERE DeptID=" & [d].[DeptID],", "," : ") AS Who
FROM Departments AS d INNER JOIN Persons AS p ON d.DeptID = p.DeptID
GROUP BY d.DeptID, d.Department, 3;
使用ADO的功能
Function ConcatADO(strSQL As String, strColDelim, strRowDelim, ParamArray NameList() As Variant)
Dim rs As New ADODB.Recordset
Dim strList As String
On Error GoTo Proc_Err
If strSQL <> "" Then
rs.Open strSQL, CurrentProject.Connection
strList = rs.GetString(, , strColDelim, strRowDelim)
strList = Mid(strList, 1, Len(strList) - Len(strRowDelim))
Else
strList = Join(NameList, strColDelim)
End If
ConcatADO = strList
Exit Function
Proc_Err:
ConcatADO = "***" & UCase(Err.Description)
End Function
这篇关于访问sql查询以连接行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文