如何在MS Access 2007或MS SQL Server 2005中通过SQL将字段转换为行

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.

我有一个遗留的MS Access 2007表，其中包含52个字段(一年中的每个星期一个字段)表示历史销售数据(实际上还有一个字段)。我想把这个数据库转换成更传统的时间/值列表。

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

有没有人知道如何不用52+显式参数编写查询?

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

(如果在MS SQL Server 2005下存在解决方案，我还可以导出/导入表)

3 个解决方案

#1

Using PIVOT and UNPIVOT.

使用主和透视。

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.

UNPIVOT通过将列旋转成行来执行几乎与PIVOT相反的操作。假设前面示例中生成的表作为pvt存储在数据库中，并希望将列标识符Emp1、Emp2、Emp3、Emp4和Emp5旋转为与特定供应商对应的行值。这意味着您必须识别另外两个列。包含要旋转的列值(Emp1、Emp2、…)的列将被称为Employee，保存当前驻留在被旋转的列下的值的列将被称为Orders。这些列分别对应于Transact-SQL定义中的pivot_column和value_column。这是查询。

--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)
GO
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)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO

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
...

#2

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

如上所述，UNPIVOT操作符(如果可用)将执行以下操作……如果没有，那么std SQL方法是:

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
    UNION
  Select 2 as week, Week2Val as value from Table
    UNION
  Select 3 as week, Week3Val as value from Table
    UNION
 ... 
    UNION
  Select 52 as week, Week52Val as value from Table

#3

No need to export to SQL Server. In Access, try the /View/PivotTable View submenu. (It's in my Access 2003, at any rate.) I like it better than the one in Excel.

不需要导出到SQL Server。在Access中，尝试/View/PivotTable View子菜单。(不管怎么说，这是我2003年的访问权限。)我比Excel中的那个更喜欢。

#1