如何在 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 个字段)代表历史销售数据(实际上加上一个字段).我想将此数据库转换为更传统的时间/值列表.

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

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

解决方案

使用 PIVOT 和 UNPIVOT.

<块引用>

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

--创建表并插入值，如上例所示.CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,Emp3 int、Emp4 int、Emp5 int)去插入 pvt 值 (1,4,3,5,4,4)插入 pvt 值 (2,4,1,5,5,5)插入 pvt 值 (3,4,3,5,4,4)插入 pvt 值 (4,4,2,5,5,4)插入 pvt 值 (5,5,1,5,5,5)去--取消透视表.选择供应商 ID、员工、订单从(选择供应商 ID、Emp1、Emp2、Emp3、Emp4、Emp5来自 pvt) p反透视(给员工的订单(Emp1, Emp2, Emp3, Emp4, Emp5))AS unpvt去

<块引用>

这是部分结果集.

VendorID 员工订单1 员工 1 41 员工 2 31 员工 3 51 员工 4 41 员工 5 42 员工 1 42 员工 2 12 员工 3 52 员工 4 52 员工 5 5...

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)

解决方案

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.

--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屋！