SQL2005行列转换的另一方法 PIVOT

最新推荐文章于 2025-12-04 15:14:39 发布

原创最新推荐文章于 2025-12-04 15:14:39 发布 · 450 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#pivot #sql #insert #table

Ms SQL Server 专栏收录该内容

115 篇文章

订阅专栏

本文介绍了一个关于销售数据的数据库表构建过程及数据插入操作，并展示了如何使用Pivot来汇总不同月份的销售金额。

CREATE TABLE salesByMonth
(
year char(4),
month char(3),
amount money,
PRIMARY KEY (year, month)
)

INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Jan', 789.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Feb', 389.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Mar', 8867.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Apr', 778.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','May', 78.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Jun', 9.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Jul', 987.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Aug', 866.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Sep', 7787.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Oct', 85576.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Nov', 855.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2004','Dec', 5878.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Jan', 7.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Feb', 6868.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Mar', 688.0000)
INSERT INTO salesByMonth (year, month, amount)
VALUES('2005','Apr', 9897.0000)
SELECT *
-- [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM ( SELECT amount, month
FROM salesByMonth ) AS salesByMonth
PIVOT ( SUM(amount) FOR month IN
([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) AS ourPivot