在付款行下将此两行转换为两列
要减少行数,增加列数,新列基于另一列的值,您需要"条件聚合"。
本质上,这仅仅意味着将case expression
放在聚合函数(如SUM()
,例如SUM(case when name='Fred' then col_value end) as sum_value
)中。所以我想它看起来应该像这样:
SELECT
ft.ApplicationId
, min(ft.PostingDate) AS min_postingDate
, min(ft.ValueDate) AS min_valueDate
, sum(CASE WHEN description = 'Payment ' THEN ft.CreditAmount END) AS CreditAmount
, sum(CASE WHEN description = 'Capital Credit Memo' THEN ft.CreditAmount END) AS CapitalAmount
, sum(CASE WHEN description = 'Interest Credit Memo' THEN ft.CreditAmount END) AS InterestAmount
FROM [dbo].[FinancialTransaction] AS ft
LEFT JOIN TransactionType AS TT ON tt.TransactionTypeId = ft.TransactionTypeId
LEFT JOIN [application] AS a ON a.ApplicationId = ft.ApplicationId
LEFT JOIN [entity] AS c ON c.Entityid = a.CustomerEntityId
GROUP BY
ft.ApplicationId
我很可能没有得到正确的大小写表达式,因为一个或多个可能需要使用DebitAmount,并且可能包含多个描述值,例如
, sum(CASE WHEN description IN ('Payment','Return') THEN ft.CreditAmount END) AS CreditAmount
希望这足以让您实现所需的逻辑。