MS Access 相当于 T-SQL 中的 FOR XML PATH 以列出相关值

问题描述

我在访问中有一个如下表:

I Have a table as follows in access:

MatterNO    Description     Amount
FL069509    SMS             R 50.00
FL069509    Call            R 52.00
FL069509    Summons         R 52.00
FL069509    Email           R 5 420.00
FL069509    Letter          R 0.12
NA076353"   SMs             R 123.10
NA076353"   call            R 0.53
NA076353"   summons         R 53.00
NA076353"   email           R 453.00
NA076353"   letter          R 4 530.00
NA076359    Call            R 50.00

我想对 MatterNO 进行分组并汇总金额，但在单独列 id 中的金额旁边希望显示构成该金额的所有描述的列表.

I would like to group the MatterNO's and sum up the Amounts, but next to the amount in a separate column id like to display a list of all the descriptions that make up that amount.

例如:

MatterNO    Amount    CSV
FL069509    R5574.12  SMS, Call, Summons, Email, Letter

我知道在 SQL 中有一个名为 FOR XML PATH 的函数

I know in SQL theres a function called FOR XML PATH

如下图:

SELECT t.MatterNo,SUM(Amount), STUFF(
(SELECT ',' + s.Descriptions
FROM Table1 s
WHERE s.MatterNo = t.MatterNo
FOR XML PATH('')),1,1,'') AS CSV
FROM Table1 AS t
GROUP BY t.MatterNo

是否可以在访问中执行此操作?

Is it possible to do this in access?

我从下面的评论中使用 ConcatRelated() 添加了这个

I added this from the comment below with ConcatRelated()

SELECT DISTINCT table1.MatterNO, Sum(table1.Amount) AS SumOfAmount,
ConcatRelated("Description","table1","MatterNo = '" & [MatterNo] & "'") AS Descriptions
FROM table1
GROUP BY table1.MatterNO, table1.Description;

我得到了我需要的东西.

And i get what i need.

但是有一个问题，不是把金额加起来，我该如何解决这个问题?

However one problem, it is not adding the amounts up, how can i fix this?

推荐答案

试试这个:

SELECT
    MatterNO,
    SumOfAmount,
    ConcatRelated("Description","table1","MatterNo = '" & [MatterNo] & "'") AS Descriptions
FROM
    (
        SELECT
            MatterNO,
            SUM(Amount) AS SumOfAmount
        FROM table1
        GROUP BY MatterNO
    ) AS subquery

这篇关于MS Access 相当于 T-SQL 中的 FOR XML PATH 以列出相关值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！