I have a table similare to the one below.
I need to separate it into two tables/result sets for audit.
If see sample row ID(1) goes from division 1 to division 9, then on row (ID 10), there is a transfer from 9 back to 1.
I need to split those two into separate tables. they have the same period, year just from/to are reversed.
| FromDivision | ToDivision | PeriodOfTransfer | YearOfTransfer | Amount | TransferCurrency |
|---|---|---|---|---|---|
| Division 1 | Division 9 | 2 | 2018 | 100 | CAD |
| Division 2 | Division 10 | 2 | 2018 | 100 | CAD |
| Division 3 | Division 11 | 3 | 2018 | 100 | CAD |
| Division 4 | Division 12 | 5 | 2018 | 100 | CAD |
| Division 5 | Division 13 | 5 | 2018 | 100 | CAD |
| Division 6 | Division 14 | 5 | 2018 | 100 | USD |
| Division 7 | Division 15 | 7 | 2018 | 100 | USD |
| Division 8 | Division 16 | 8 | 2018 | 100 | USD |
| Division 9 | Division 1 | 2 | 2018 | 100 | CAD |
| Division 10 | Division 2 | 2 | 2018 | 100 | CAD |
| Division 11 | Division 3 | 3 | 2018 | 122 | CAD |
| Division 12 | Division 4 | 5 | 2018 | 100 | CAD |
| Division 13 | Division 5 | 5 | 2018 | 333 | USD |
| Division 14 | Division 6 | 5 | 2018 | 100 | USD |
| Division 15 | Division 7 | 7 | 2018 | 100 | USD |
| Division 16 | Division 8 | 8 | 2018 | 100 | CAD |
desired output:
table One
| ID | FromDivision | ToDivision | PeriodOfTransfer | YearOfTransfer | Amount | TransferCurrency |
|---|---|---|---|---|---|---|
| 1 | Division 1 | Division 9 | 2 | 2018 | 100 | CAD |
| 2 | Division 2 | Division 10 | 2 | 2018 | 100 | CAD |
| 3 | Division 3 | Division 11 | 3 | 2018 | 100 | CAD |
| 4 | Division 4 | Division 12 | 5 | 2018 | 100 | CAD |
| 5 | Division 5 | Division 13 | 5 | 2018 | 100 | CAD |
| 6 | Division 6 | Division 14 | 5 | 2018 | 100 | USD |
| 7 | Division 7 | Division 15 | 7 | 2018 | 100 | USD |
| 8 | Division 8 | Division 16 | 8 | 2018 | 100 | USD |
table 2
| 10 | Division 9 | Division 1 | 2 | 2018 | 100 | CAD |
|---|---|---|---|---|---|---|
| 11 | Division 10 | Division 2 | 2 | 2018 | 100 | CAD |
| 12 | Division 11 | Division 3 | 3 | 2018 | 122 | CAD |
| 13 | Division 12 | Division 4 | 5 | 2018 | 100 | CAD |
| 14 | Division 13 | Division 5 | 5 | 2018 | 333 | USD |
| 15 | Division 14 | Division 6 | 5 | 2018 | 100 | USD |
| 16 | Division 15 | Division 7 | 7 | 2018 | 100 | USD |
| 17 | Division 16 | Division 8 | 8 | 2018 | 100 | CAD |
Any deas?