如何通过MS Access 2007或MS SQL Server 2005中的SQL将字段转换为行 [英] How to convert fields into rows through SQL in MS Access 2007 or MS SQL Server 2005

问题描述

我有一个旧版MS Access 2007表，该表包含52个字段(每年每个星期1个字段)，这些字段代表历史销售数据(实际是一年中的一个字段).我想将此数据库转换为更常规的时间/值列表.

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.

有人知道如何在不编写具有52个以上显式参数的查询的情况下做到这一点吗?

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

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

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

推荐答案

UNPIVOT几乎执行相反的操作 通过旋转操作PIVOT 列成行.假设桌子 在前面的示例中产生的是 作为pvt存储在数据库中，并且您 想要旋转列标识符 Emp1，Emp2，Emp3，Emp4和Emp5进入 对应于 特定的供应商.这意味着你 必须标识另外两列. 将包含 您正在旋转的列值 (Emp1，Emp2，...)将被调用 Employee，以及将要显示的列 保留当前驻留的值 在旋转的列下将 被称为订单.这些专栏 对应于 pivot_column 和 分别在 value_column Transact-SQL定义.这里是 查询.

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

这篇关于如何通过MS Access 2007或MS SQL Server 2005中的SQL将字段转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！