CODEWITHSUNDEEP
sqlsql-serversql-server-2005unpivot
Mayou
Mayou

Reputation: 8818

Converting a pivot table to a flat table in SQL

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

enter image description here

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:

enter image description here

Is there an easy way of doing that?

Upvotes: 1

Views: 1554

Answers (2)

Taryn
Taryn

Reputation: 247650

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

Upvotes: 2

Kobi
Kobi

Reputation: 2524

select employee_id,employee_name,data,old,new
from (
select employee_id,employee_name,adress_old as old,adress_new as new,'ADRESS' as data
from employe
union
select employee_id,employee_name,income_old,income_new,'INCOME'
from employe
 ) data
order by employee_id,data

see this fiddle demo : http://sqlfiddle.com/#!2/64344/7/0

Upvotes: 1

Related Questions