Sql 查询中获取审批者不起作用

我有一个表:InvoiceDisputesApprovalResult，其中包含工作流. 它的列是:disputeID、ApproverEmail、Status、ApprovalLevel和ClaimID

例如，索赔ID=3423和争议ID=3123123具有以下内容:

disputeId  |approverEmail           |status  |approvalLevel|claimId
3123123    |approver1@mailinator.com|waiting |1            |3423 
3123123    |approver2@mailinator.com|waiting |2            |3423 
3123123    |approver3@mailinator.com|waiting |3            |3423 

因此，有争议的ID 3123123有3个批准级别.

所需的JSON输出:所需的结果是获得此JSON(对于ClaimID 3423，应该是)

{   "claimId":  3423 ,  "disputeId":    3123123 ,   "disputeType": "CREAR_NC",  "status": "OK",  "description" : "Aprobador",   "approvers": [  {   "mail": "approver1@mailinator.com", "approvalLevel":    1   },{ "mail": "approver2@mailinator.com", "approvalLevel":    2   },{ "mail": "approver3@mailinator.com", "approvalLevel":    3   }], "pvp":  null    ,"salePrice":   null    ,"saleMKUP":    null    ,"claimMKUP":   null    ,"mktope":  null    ,"mkcliete":    null    }

注:

Values: "disputeType": "CREAR_NC",  "status": "OK",  "description" : "Aprobador", "pvp":    null    ,"salePrice":   null    ,"saleMKUP":    null    ,"claimMKUP":   null    ,"mktope":  null    ,"mkcliete":    null are harcoded. 你明白我的意思吗？

另一个例子是存在争议ID=221322

disputeId |approverEmail           |status  |approvalLevel|claimId
221322    |approver1@mailinator.com|waiting |1            |2323
221322    |approver2@mailinator.com|waiting |2            |2323

但在本例中，它只有2个ApprovalLevel.

期望的结果是获得此JSON(对于ClaimID 2323，它应该是)

{   "claimId":  2323,   "disputeId":    221322, "disputeType": "CREAR_NC",  "status": "OK",  "description" : "Aprobador",   "approvers": [  {   "mail": "approver1@mailinator.com", "approvalLevel":    1   },{ "mail": "approver2@mailinator.com", "approvalLevel":    2   }], "pvp":  null    ,"salePrice":   null    ,"saleMKUP":    null    ,"claimMKUP":   null    ,"mktope":  null    ,"mkcliete":    null    }

同样，值:"conpositteType":"CREAR_NC"，"Status":"OK"，"Description":"Aproador"，"pvp":NULL，"salePrice":NULL，"saleMKUP":NULL，"CLUMKUP":NULL，"mkope":NULL，"mkcliete":NULL.

你明白我的意思吗？

我试过这样的方法:

SELECT
    main.claimId,
    main.disputeId,
    'CREAR_NC' AS disputeType,
    'OK' AS status,
    'Aprobador' AS description,
    (
        SELECT
            '[' + STUFF(
                (
                    SELECT
                        ', ' + (
                            SELECT '{"mail": "' + sub.approverEmail + '", "approvalLevel": ' + CAST(sub.approvalLevel AS VARCHAR) + '}'
                            FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)')
                    FROM InvoiceDisputesApprovalResult AS sub
                    WHERE sub.claimId = main.claimId
                    and main.disputeId = 28258
                    FOR XML PATH('')
                ), 1, 2, ''
            ) + ']'
    ) AS approvers,
    NULL AS pvp,
    NULL AS salePrice,
    NULL AS saleMKUP,
    NULL AS claimMKUP,
    NULL AS mktope,
    NULL AS mkcliete
FROM InvoiceDisputesApprovalResult AS main
WHERE main.status = 'WAITING'
  AND main.disputeId = 28258 
GROUP BY main.claimId, main.disputeId;

问题是我得到了许多不属于给定争议ID的审批者

claimId              disputeId            disputeType status description approvers                                                                                                                                                                                                                                                        pvp         salePrice   saleMKUP    claimMKUP   mktope      mkcliete

(1行受影响)