22,206
社区成员
发帖
与我相关
我的任务
分享
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 * from salesByMonth
--按照常规行转列
SELECT *
FROM salesByMonth
PIVOT ( SUM(amount) FOR month IN
([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
)t
显示结果
year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
---- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
2004 789.00 389.00 8867.00 778.00 78.00 9.00 987.00 866.00 7787.00 85576.00 855.00 5878.00
2005 7.00 6868.00 688.00 9897.00 NULL NULL NULL NULL NULL NULL NULL NULL
(2 行受影响)
而我的需求是相反的操作,我要求把year作为列显示,呈现类似如下的结果:
2004 2005
Jan X X
Feb
Mar
Apr
May
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 *
FROM salesByMonth
PIVOT ( SUM(amount) FOR YEAR IN
([2004],[2005])
)t
drop table salesByMonth
/*
month 2004 2005
----- --------------------- ---------------------
Apr 778.00 9897.00
Aug 866.00 NULL
Dec 5878.00 NULL
Feb 389.00 6868.00
Jan 789.00 7.00
Jul 987.00 NULL
Jun 9.00 NULL
Mar 8867.00 688.00
May 78.00 NULL
Nov 855.00 NULL
Oct 85576.00 NULL
Sep 7787.00 NULL
(12 行受影响)
*/