How to convert fields into rows through SQL in MS Access 2007 or MS SQL Server 2005

I have a legacy MS Access 2007 table that contains 52 fields (1 field for each week of the year) representing historical sales data (plus one field for the year actually). I would like to convert this database into a more conventional Time/Value listing.

Does anyone knows how to do that without writing queries with 52+ explicit parameters?

(if a solution exists under MS SQL Server 2005, I can also export/import the table)



UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. Here is the query.

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
--Unpivot the table.
SELECT VendorID, Employee, Orders
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt

Here is a partial result set.

VendorID   Employee   Orders
1      Emp1         4
1      Emp2         3
1      Emp3         5
1      Emp4         4
1      Emp5         4
2      Emp1         4
2      Emp2         1
2      Emp3         5
2      Emp4         5
2      Emp5         5

As mentioned above, UNPIVOT operator, if available, will do this... If this is not available, then std SQL approach is:

Union multiple select statments (One for each week) that alias the specific week's column with the same column name alias

  Select 1 as week, Week1Val as value from Table
  Select 2 as week, Week2Val as value from Table
  Select 3 as week, Week3Val as value from Table
  Select 52 as week, Week52Val as value from Table

Need Your Help

Strange indentation upon code formatting with MonoDevelop (double indentation)

mono unity3d monodevelop

When I use code formatting with MonoDevelop (Shortcut: Ctrl+I), everything gets double-indented:

php doctrine behavior help

php doctrine

I'm going through tutorials for doctrine and have come across a problem. Does the autoload function not apply on behaviours?