Sql row separation

Question

Table Schema:

Create Table
(
Transaction CHAR(18),
Serial INT,
Project CHAR(3),
Amount MONEY
CONSTRAINT [PK_TRANSACTION_SERIAL] PRIMARY KEY CLUSTERED
(
Transaction ASC,
Serial ASC
)
)

Data set:

+-----------------+-------+---------+---------+
|     Transaction | Serial| Project | Amount  |
+-----------------+-------+---------+---------+
| A00000000000001 |     1 |     100 |     500 |
| A00000000000001 |     2 |     200 |    -200 |
| A00000000000001 |     3 |     200 |    -100 |
| A00000000000001 |     4 |     101 |    -200 |
| A00000000000002 |     1 |     100 |     100 |
| A00000000000002 |     2 |     101 |    -100 |
| A00000000000003 |     1 |     100 |     300 |
| A00000000000003 |     2 |     200 |    -300 |
| A00000000000004 |     1 |     200 |    -200 |
| A00000000000004 |     2 |     100 |     100 |
| A00000000000004 |     3 |     101 |     100 |
| A00000000000005 |     1 |     200 |     200 |
| A00000000000005 |     2 |     100 |    -300 |
| A00000000000005 |     3 |     101 |     100 |
+-----------------+-------+---------+---------+

For any transaction there will be either 1 positive balance against multiple negative balance or 1 negative against multiple positive. There will be no transaction as many positive amount against many negative amount.

Transactions with 1 positive and 1 negative amount is the best case scenario.

My target is to make all transactions 1 to 1 as following.

In first row at output amount for project 200 is merged as 2nd and 2rd row of dataset has same transaction ans same project.

Here the max ABS(amount) row for each transaction is being splitted into multiple rows according to the value of amount of other rows of that transaction.

Output:

+-----------------+---------+---------+
|     Transaction | Project | Amount  |
+-----------------+---------+---------+
| A00000000000001 |     100 |     300 |
| A00000000000001 |     200 |    -300 |
---------------------------------------
| A00000000000001 |     100 |     200 |
| A00000000000001 |     101 |    -200 |
---------------------------------------
| A00000000000002 |     100 |     100 |
| A00000000000002 |     101 |    -100 |
---------------------------------------
| A00000000000003 |     100 |     300 |
| A00000000000003 |     200 |    -300 |
---------------------------------------
| A00000000000004 |     200 |    -100 |
| A00000000000004 |     100 |     100 |
---------------------------------------
| A00000000000004 |     200 |    -100 |
| A00000000000004 |     101 |     100 |
---------------------------------------
| A00000000000005 |     200 |     200 |
| A00000000000005 |     100 |    -200 |
---------------------------------------
| A00000000000005 |     101 |     100 |
| A00000000000005 |     100 |    -100 |
+-----------------+---------+---------+

I am using SQL SERVER 2012 or higher.

Answer 1

I am not sure about Row separation approach but for getting this 1-1 (negative and positive) value you can order your output.

you can order based on transaction and then absolute value of your amount like -

select * from <your_table_name> order by Transaction, abs(Amount)