By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into data
from (values
('abc123', 100, 5, 45, 20, 55),
('abc124', 60, 5, 60, 20, NULL),
('abc125', 50, 5, NULL, 22, 50)
) v(InvoiceNo, TotalAmount, Percentage1, Amount1, Percentage2, Amount2 )
3 rows affected
select invoiceno + v.suffix, v.percentage, v.amount,
v.taxratetype, v.referencevalue
from data d cross apply
(values (1, d.Percentage1, d.Amount1, 'EXEMPT', ''),
(2, d.Percentage2, d.Amount2, 'TAXABLE', (case when d.amount1 is not null then '_1' else '' end))
) v(ReferenceValue, Percentage, Amount, TaxRateType, Suffix)
where amount is not null;
| (No column name) | percentage | amount | taxratetype | referencevalue |
|---|---|---|---|---|
| abc123 | 5 | 45 | EXEMPT | 1 |
| abc123_1 | 20 | 55 | TAXABLE | 2 |
| abc124 | 5 | 60 | EXEMPT | 1 |
| abc125 | 22 | 50 | TAXABLE | 2 |