为表中的每一行返回两行
提问于 2019-06-06 15:02:25
我有一个事务表，希望每行返回两行。transaction表中的每个事务都有一行。我想以以下日记帐分录格式返回交易日期和金额：
TransactionDate TransactionRef Transaction Amount
01/01/2019 12345 500.5我还需要包括借方/贷方列，以使第一行过帐为正金额，借方/贷方显示'Db‘，然后返回的第二行将是负数，借方/贷方显示'Cr’
我需要查询Transaction表并返回以下值：
TransactionDate TransactionRef Transaction Amount Db/Cr
01/01/2019 12345 500.5 Db
01/01/2019 12345 -500.5 Cr
回答 6
Stack Overflow用户
回答已采纳
发布于 2019-06-06 15:10:21
使用UNION ALL：
select
*,
case when TransactionAmount > 0 then 'Db' else 'Cr' end AS [Db/Cr]
from tablename
union all
select
TransactionDate,
TransactionRef,
-1.0 * TransactionAmount,
case when TransactionAmount > 0 then 'Cr' else 'Db' end
from tablename
order by TransactionDate, TransactionRef, TransactionAmount descStack Overflow用户
发布于 2019-06-06 15:06:56
另一种选择是交叉应用
Select A.TransactionDate
,A.TransactionRef
,B.TransactionAmount
,DrCr = case when sign(B.TransactionAmount)<0 then 'Cr' else 'Dr' end
From YourTable A
Cross Apply ( values ( TransactionAmount)
,( TransactionAmount*-1)
) B(TransactionAmount)Stack Overflow用户
发布于 2019-06-06 17:26:39
Select
coalesce(t1.transactiondate,t2.transactiondate) as transdate,
coalesce(t1.transactionref,t2.transactionref) as transref,
coalesce(t1.transactionamount,-1*ABS(t2.transactionamount)) as transamount
from tansaction t1 full outer join transaction t2
on 1 = 0
order by
TransactionRef,
transactiondate,
SIGN(t1.transactionamount)desc,
ABS(t2.transactionamount)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56480300
复制
腾讯云开发者
