Converting a pivot table to a flat table in SQL

Question

I would like to transform a pivot table into a flat table, but in the following fashion: consider the simple example of this table:

As you can see, for each item - Address or Income -, we have a column for old values, and a column for new (updated values). I would like to convert the table to a "flat" table, looking like:

Is there an easy way of doing that?

Answer 1

In order to get the result, you will need to UNPIVOT the data. When you unpivot you convert the multiple columns into multiple rows, in doing so the datatypes of the data must be the same.

I would use CROSS APPLY to unpivot the columns in pairs:

select t.employee_id,
  t.employee_name,
  c.data,
  c.old,
  c.new
from yourtable t
cross apply
(
  values 
  ('Address', Address_Old, Address_new),
  ('Income', cast(income_old as varchar(15)), cast(income_new as varchar(15)))
) c (data, old, new);

See SQL Fiddle with demo. As you can see this uses a cast on the income columns because I am guessing it is a different datatype from the address. Since the final result will have these values in the same column the data must be of the same type.

This can also be written using CROSS APPLY with UNION ALL:

select t.employee_id,
  t.employee_name,
  c.data,
  c.old,
  c.new
from yourtable t
cross apply
(
  select 'Address', Address_Old, Address_new union all
  select 'Income', cast(income_old as varchar(15)), cast(income_new as varchar(15))
) c (data, old, new)

See Demo