SQL - Convert Columns of Each Row to New Table Format

Question

I can't seem to find a more direct solution to this. I'm trying to take a table with multiple fields and "pivot" that info to a table with a more flexible format for the data.

I have the following summarized table:

Item | Info1 | Info2 | Date1 | Date2
-------------------------------------
item1  info11  info12  date11  date12
item2  info21  info22  date21  date22

Pretty much I want this table to be a "many" table instead of "one", and want the following:

Item | Info | Date 
--------------------
item1  info11 date11
item1  info12 date12
item2  info21 date21
item2  info22 date22

The table I'm working with has way more fields, but this is basically the concept I'm struggling with in SQL code. I can imagine running a while loop to perform the data pivot for each record, or maybe there is something easier?

Answer 1

This actually isn't a PIVOT. The PIVOT function converts rows into columns, you want to UNPIVOTwhich converts multiple columns into rows.

Since you are using SQL Server 2008, you can use CROSS APPLY to unpivot the pairs of columns:

select 
  c.item,
  c.info,
  c.date
from yourtable t
cross apply
(
  select item, info1, date1 union all
  select item, info2, date2
) c (item, info, date);

See SQL Fiddle with Demo

This could also be done using a UNION ALL query:

select item, info1, date1
from yourtable
union all
select item, info2, date2
from yourtable

See SQL Fiddle with Demo