SQL Ms Access Query：通过查询连接行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5517233/
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
Ms Access Query: Concatenating Rows through a query
提问by reggie
Suppose I have table in Ms Access with following information:
假设我在 Ms Access 中有表，其中包含以下信息：
ColumnA ColumnB
1 abc
1 pqr
1 xyz
2 efg
2 hij
3 asd
My question is, how can I concatenate the values in the second column to a row value based on the first column. The query results that I want is as follows:
我的问题是，如何将第二列中的值连接到基于第一列的行值。我想要的查询结果如下：
ColumnA ColumnB
1 abc, pqr, xyz
2 efg, hij
3 asd
I want to achieve this through a query. Can someone help me attain this?
我想通过查询来实现这一点。有人可以帮助我实现这一目标吗？
采纳答案by Thomas
You need a function to do the concatenation.
您需要一个函数来进行连接。
Microsoft Access condense multiple lines in a table
Example using your data:
使用您的数据的示例：
Select T.ColumnA
, GetList("Select ColumnB From Table1 As T1 Where T1.ColumnA = " & [T].[ColumnA],"",", ") AS ColumnBItems
From Table1 AS T
Group By T.ColumnA;
回答by Deborah Cole
Here's an outstanding link re: how to do this from within SQL by calling a function. The instructions are exceptionally clear & the function is written for you so you can just copy, paste & go. Even someone with no knowledge of VB can easily implement it: Concatenate values from related records
这是一个出色的链接：如何通过调用函数从 SQL 中执行此操作。说明非常清晰，功能是为您编写的，因此您只需复制、粘贴和运行即可。即使不了解 VB 的人也可以轻松实现它： 连接相关记录中的值
回答by Patrick
this can be very difficult to obtain. If you MUST do it in a query and not a function, the problem that you will run into is the limit of the number of rows you can concatenate into one column. So far the only way that i have found to achieve this is via iif statements.
这可能很难获得。如果您必须在查询中而不是在函数中执行此操作，那么您将遇到的问题是您可以连接到一列中的行数的限制。到目前为止，我发现实现这一目标的唯一方法是通过 iif 语句。
SELECT
test1.ColumnA AS ColumnA,
First([test1].[ColumnB]) & IIf(Count([test1].[ColumnB])>1,"," & Last([test1].[ColumnB])) AS ColumnB
FROM test1
GROUP BY test1.ColumnA;
returns:
返回：
ColumnA ColumnB
1 abc,xyz
2 efg,hij
3 asd
This will return the first and the last only, but I'm sure with a little work you could work out the Choose function, but like I said you would have to add more iif statements for each additional item you want to add, hence the limitation.
这将只返回第一个和最后一个，但我相信你可以通过一些工作来计算出选择函数，但就像我说的那样，你必须为要添加的每个附加项目添加更多的 iif 语句，因此局限性。
回答by Will
The table could have a sequence column, which gives it a unique primary key of ColumnA-sequence:
该表可以有一个序列列，它给它一个唯一的 ColumnA-sequence 主键：
table: t1
ColumnA sequence ColumnB
1 1 abc
1 2 pqr
1 3 xyz
2 1 efg
2 2 hij
3 1 asd
And a Crosstab could be created:
并且可以创建一个交叉表：
query: x1
TRANSFORM Min([columnB] & ", ") AS Expr1
SELECT t1.columnA
FROM t1
GROUP BY t1.columnA
PIVOT t1.sequence;
columnA 1 2 3
1 abc, pqr, xyz,
2 efg, hij,
3 asd,
Then a final query can combine the columns and remove the last comma:
然后最终查询可以组合列并删除最后一个逗号：
SELECT x1.columnA, Left([1] & [2] & [3],Len([1] & [2] & [3])-2) AS columnB FROM x1;
columnA columnB
1 abc, pqr, xyz
2 efg, hij
3 asd
To automate filling in the sequence, the following VBA code can be used:
要自动填充序列，可以使用以下 VBA 代码：
Sub fill_sequence_t1()
Dim i: i = 1
Do While DCount("*", "t1", "sequence IS NULL") > 0
DoCmd.RunSQL "SELECT t1.columnA, Min(t1.columnB) AS columnB_min INTO t2" & _
" FROM t1 WHERE t1.sequence IS NULL GROUP BY t1.columnA;"
DoCmd.RunSQL "UPDATE t1 INNER JOIN t2 ON (t1.columnA = t2.columnA)" & _
" AND (t1.columnB = t2.columnB_min) SET t1.sequence=" & i
CurrentDb.TableDefs.Delete "t2"
i = i + 1
Loop
End Sub
