Unpivot tax data

Question

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?

Answer 1

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

See SQL Fiddle with Demo

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 |