Calculation view for material to material transfer

I have a table Tab1 with two rows

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

Desired Result

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

I am working on SAP HANA, writing a calculation view.

Answer

You can use conditional aggregation as follows:

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

Advertisement

Answer