SQL，合并结果 [英] SQL, combining the result

问题描述

我正在使用Access并拥有此SQL

I am using Access and have this SQL

SELECT land.id, land.official_name, vaksiner.vaksiner
FROM land INNER JOIN (vaksiner INNER JOIN land_sykdom ON vaksiner.id = land_sykdom.sykdom)        ON land.kort = land_sykdom.land
ORDER BY land.official_name

SQL给我这样的结果:

The SQL gives me a result like this:

id    official_name    vaksiner

1     a                A
1     a                C    
2     b                A
2     b                B
2     b                C

但是我想合并结果，使其看起来像这样:

But I want to combine the result so that it looks like this:

id    official_name    vaksiner

1     a                A, C
2     b                A, B, C

推荐答案

请参阅Allen Browne的ConcatRelated函数(串联相关记录中的值)

See Allen Browne's ConcatRelated function (Concatenate values from related records)

如果将现有查询另存为 qryVaksinerRaw ，则可以像这样构建新查询以产生所需的结果.

If you save your existing query as qryVaksinerRaw, you can build a new query like this to produce the results you're after.

SELECT DISTINCT
    id,
    official_name,
    ConcatRelated("vaksiner","qryVaksinerRaw","id = " & [id]) AS vaksiner
FROM qryVaksinerRaw;

要将功能添加到模块，请从网页开始复制功能，

To add the function to a module, copy the function from the web page starting with

Public Function ConcatRelated(strField As String, _

并继续包含

End Function

然后将复制的文本粘贴到您的模块中.

Then paste the copied text into your module.

注意ConcatRelated()然后将可用于在Access内部运行的查询.但是，像任何自定义VBA函数一样，它不能用于从Access外部运行的查询(例如经典的ASP，.Net等)

Note ConcatRelated() will then be available for queries run from inside Access. However, like any custom VBA function, it can not be used in queries run from outside Access (like classic ASP, .Net, etc.)

这篇关于SQL，合并结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！