SQL 2005中pivot and unpivot，行列互转实例

最新推荐文章于 2025-08-07 14:59:42 发布

deepwishly

最新推荐文章于 2025-08-07 14:59:42 发布

阅读量777

点赞数

CC 4.0 BY-SA版权

文章标签： pivot sql insert 测试 table 报表

本文链接：https://blog.youkuaiyun.com/deepwishly/article/details/5265857

本文介绍如何使用PIVOT和UNPIVOT进行数据透视和反透视操作，通过具体实例展示了如何将行转换为列或将列转换为行的方法，适用于汇总和展示多维度数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如题所示：需要生成交叉表格报表以汇总数据时。常用到 PIVOT ，例如，假设需要在 `AdventureWorks` 示例数据库中查询 `PurchaseOrderHeader` 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表（按供应商划分）在透视子句中指定的每个 `EmployeeID` 号（在本例中为雇员 164、198、223、231 和 233）都有相应的一列如图所示：

这意味着 `EmployeeID` 列返回的唯一值自行变成了最终结果集中的字段。

Code
USE AdventureWorks;
GO
SELECT VendorID, [164] as '员工164', [198] as '员工198', [223] as '员工223', [231] as '员工231', [233] as '员工233'
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

SELECT VendorID,EmployeeID,count(VendorID) as 'count'
FROM Purchasing.PurchaseOrderHeader
where EmployeeID in (164,198,223,231,233)
group by VendorID,EmployeeID
ORDER BY VendorID,EmployeeID

具体事例，注意红色标识部分

创建测试表，插入测试数据

create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,''a'',1,1000)
insert into test values(1,''a'',2,2000)
insert into test values(1,''a'',3,4000)
insert into test values(1,''a'',4,5000)
insert into test values(2,''b'',1,3000)
insert into test values(2,''b'',2,3500)
insert into test values(2,''b'',3,4200)
insert into test values(2,''b'',4,5500)

select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500

(8 row(s) affected)

利用PIVOT将个季度的利润转成横向显示：

select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt

id name 一季度二季度三季度四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

(2 row(s) affected)

UNPIVOT

建立测试表，插入测试数据

drop table test

create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)

insert into test values(1,''a'',1000,2000,4000,5000)
insert into test values(2,''b'',3000,3500,4200,5500)

select * from test

id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

(2 row(s) affected)

利用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：

select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500

(8 row(s) affected)