Sql 有没有一种方法可以将始终遵循序列的单个字段的值组合起来，以创建每个 ID 的所有移动

STRING_AGG函数应该能够实现这一点.

每个Value_New应由' -> '分隔，并应按Changed_Date字段排序.AGROUP BY将需要包括在内.

select ac."id_Parent_Record"
,ac."Field_Changed"
, STRING_AGG(ac."Value_New", ' -> ' ORDER BY ac."Changed_Date" asc) AS "Transitions"
from public.api_changelog ac
where ac."Field_Changed" IN ('_id_Matter','Task_Workflow')
and ac."id_Parent_Record" = '637454609993200'
GROUP BY ac."id_Parent_Record"
,ac."Field_Changed"
;

输出:

可以使用CTE将表重新联接回原始结果集

WITH transitions AS (
    select ac."id_Parent_Record"
    ,ac."Field_Changed"
    , STRING_AGG(ac."Value_New", ' -> ' ORDER BY ac."Changed_Date" asc) AS "Transitions"
    from public.api_changelog ac
    where ac."Field_Changed" IN ('_id_Matter','Task_Workflow')
    and ac."id_Parent_Record" = '637454609993200'
    GROUP BY ac."id_Parent_Record"
    ,ac."Field_Changed"
)

select ac."id_Parent_Record"
,ac."Field_Changed"
,ac."Value_Previous"
,ac."Value_New"
,ac."Changed_Date" 
, t."Transitions"
from public.api_changelog ac
INNER JOIN transitions t
    ON ac."id_Parent_Record" = t."id_Parent_Record"
    AND ac."Field_Changed" = t."Field_Changed"
;