如何在t-sql中折叠空值上的行? [英] How Do I collapse rows on null values in t-sql?
本文介绍了如何在t-sql中折叠空值上的行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的查询很奇怪。我的目标是显示每个人的多次交易中的总存款和取款并显示它们。我得到多行,需要折叠成一排。所有这些都需要在一个查询中发生
I'm in a weird situation with my query. My objective is to display the total deposits and withdrawals from multiple transactions for each person and display them. I am getting multiple rows that I need to collapse into one. This all needs to happen in one query
SELECT
lastname,
firsname,
case when upper(category) = 'W' then sum(abs(principal)) end as Withdrawal,
case when upper(category) = 'D' then sum(abs(principal)) end as Deposit,
description
FROM
table1
JOIN table2 ON table1.id = table2.id
JOIN table3 ON table2.c = table3.c
WHERE
description = 'string'
GROUP BY
lastname,
firstname,
description,
category
我的结果是
lastname firstname Withdrawal Deposit description
john smith null 140.34 string
john smith 346.00 null string
jane doe null 68.03 string
jane doe 504.00 null string
我正在寻找
lastname firstname Withdrawal Deposit description
john smith 346.00 140.34 string
jane doe 504.00 68.03 string
将主体添加到组中不会工作。
adding principal into the group does not work. any help on solving this will be greatly appreciated!
推荐答案
使用条件聚合,将不胜感激。 。 。 case
是 sum()
的参数:
Use conditional aggregation . . . the case
is the argument to the sum()
:
select lastname, firsname,
sum(case when upper(category) = 'W' then abs(principal) end) as Withdrawal,
sum(case when upper(category) = 'D' then abs(principal) end) as Deposit,
description
from table1 join
table2
on table2.id = table1.id join
table3
on table3.c = table2.c
where description = 'string'
group by lastname, firstname, description
这篇关于如何在t-sql中折叠空值上的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文