I have data in this form:
department_id | VAT_id | Tax_amount | Net_amount | Gross_amount | Date | Invoice_no
1 | 3 | 10 | 90 | 100 | 20130101 | A5
1 | 8 | 5 | 35 | 40 | 20130101 | A5
3 | 3 | 5 | 45 | 50 | 20130101 | A8
And I want to transform it into:
Department_id | Vat_id | Amount | Date | Invoice_No
1 | 3 | 10 | 20130101 | A5
1 | 0 | 90 | 20130101 | A5
1 | -1 | 100 | 20130101 | A5
1 | 8 | 5 | 20130101 | A5
1 | 0 | 35 | 20130101 | A5
1 | -1 | 40 | 20130101 | A5
3 | 3 | 5 | 20130101 | A8
3 | 0 | 45 | 20130101 | A8
3 | -1 | 50 | 20130101 | A8
Vat_id value 0 is for net amount
Vat_id value -1 is for the gross amount.
How can I verticalize this data so that I can keep going forward?
You can use the UNPIVOT function to perform this:
select department_id,
case
when col = 'net_amount' then 0
when col = 'Gross_amount' then -1
else vat_id end vat_od,
amount,
invoice_no
from yourtable
unpivot
(
amount
for col in ([Tax_amount], [Net_amount], [Gross_amount])
) unpiv
See SQL Fiddle with Demo.
If you do not have access to the unpivot function, then you can use a UNION ALL query.
select department_id,
case
when col = 'net_amount' then 0
when col = 'Gross_amount' then -1
else vat_id end vat_od,
amount,
invoice_no
from
(
select department_id, vat_id,
'tax_amount' col, tax_amount amount, invoice_no
from yourtable
union all
select department_id, vat_id,
'Net_amount' col, Net_amount amount, invoice_no
from yourtable
union all
select department_id, vat_id,
'Gross_amount' col, Gross_amount amount, invoice_no
from yourtable
) src
Both queries will return:
| DEPARTMENT_ID | VAT_OD | AMOUNT | INVOICE_NO |
------------------------------------------------
| 1 | 3 | 10 | A5 |
| 1 | 0 | 90 | A5 |
| 1 | -1 | 100 | A5 |
| 1 | 8 | 5 | A5 |
| 1 | 0 | 35 | A5 |
| 1 | -1 | 40 | A5 |
| 3 | 3 | 5 | A8 |
| 3 | 0 | 45 | A8 |
| 3 | -1 | 50 | A8 |