材料到材料转移的计算视图

Question

我有一个有两行的表Tab1

+-------+----------+--------------+----------+--------+
| DOC#  | Material | Debit/Credit | Quantity | Amount |
+-------+----------+--------------+----------+--------+
| 12345 | A1       | Credit       |        5 |     50 |
| 12345 | B1       | Debit        |        5 |     50 |
+-------+----------+--------------+----------+--------+

期望结果

+-------+---------------+-------------+----------+--------+
| DOC#  | From Material | To Material | Quantity | Amount |
+-------+---------------+-------------+----------+--------+
| 12345 | A1            | B1          |        5 |     50 |
+-------+---------------+-------------+----------+--------+

我正在编写SAP HANA，编写一个计算视图。

Answer 1

您可以按以下方式使用条件聚合：

Select doc#,
       Max(case when debit_credit = 'credit' then material end) as from_material, 
       Max(case when debit_credit = 'debit' then material end) as to_material, 
       Quantity, Amount
  From t
 Group by doc#, quantity, amount

Answer 2

聚合的另一种选择是自连接。我想这对于初学者来说很容易理解。将信贷行加入到借方行，就好像它们是两个不同的表一样。

select
  doc_number, quantity, amount,
  debit.material as from_material,
  credit.material as to_material
from (select * from tab1 where credit_debit = 'Credit') as credit
join (select * from tab1 where credit_debit = 'Debit') as debit
  using (doc_number, quantity, amount)
order by doc_number, quantity, amount;

可能是HANA缺乏USING条款。那么查询就会变成

select
  credit.doc_number, credit.quantity, credit.amount,
  debit.material as from_material,
  credit.material as to_material
from (select * from tab1 where credit_debit = 'Credit') as credit
join (select * from tab1 where credit_debit = 'Debit') as debit
  on  credit.doc_number  =  debit.doc_number
  and credit.quantity    =  debit.quantity
  and credit.amount      =  debit.amount
order by credit.doc_number, credit.quantity, credit.amount;