Ms Access 查询：通过查询连接行

发布于 2024-10-29 08:24:42 字数 321 浏览 0 评论 0原文

假设我在 Ms Access 中有包含以下信息的表：

ColumnA ColumnB
1       abc
1       pqr
1       xyz
2       efg
2       hij
3       asd

我的问题是，如何将第二列中的值连接到基于第一列的行值。我想要的查询结果如下：

ColumnA ColumnB
1       abc, pqr, xyz
2       efg, hij
3       asd

我想通过查询来实现。有人可以帮助我实现这个目标吗？

原文

Suppose I have table in Ms Access with following information:

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲性难收 2024-11-05 08:24:43

您需要一个函数来进行串联。

Microsoft Access 压缩表格中的多行

使用您的数据的示例：

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;

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;

回复收藏 0 原文

三岁铭 2024-11-05 08:24:43

这是一个关于如何通过调用函数从 SQL 内部执行此操作的优秀链接。说明非常清晰&该函数是为您编写的，因此您只需复制、粘贴即可；去。即使不懂 VB 的人也可以轻松实现：
连接相关记录中的值

回复收藏 0 原文

独闯女儿国 2024-11-05 08:24:43

这可能很难获得。如果您必须在查询而不是函数中执行此操作，那么您将遇到的问题是可以连接到一列中的行数的限制。到目前为止，我发现实现此目的的唯一方法是通过 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;

ColumnA  ColumnB 
1      abc,xyz 
2      efg,hij 
3      asd

这将仅返回第一个和最后一个，但我确信只需做一些工作，您就可以计算出 Choose 函数，但就像我说的，您必须为要添加的每个附加项目添加更多 iif 语句，因此存在限制。

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.

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.

回复收藏 0 原文

方觉久 2024-11-05 08:24:43

该表可以有一个序列列，这为其提供了 ColumnA 序列的唯一主键：

table: t1
ColumnA sequence ColumnB
1       1        abc
1       2        pqr
1       3        xyz
2       1        efg
2       2        hij
3       1        asd

并且可以创建交叉表：

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,

然后最终查询可以组合列并删除最后一个逗号：

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

要自动填充序列，请执行以下操作可以使用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

The table could have a sequence column, which gives it a unique primary key of 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:

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

回复收藏 0 原文

~没有更多了~