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])
)tdrop 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 行受影响)*/
(
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])
)tdrop 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 行受影响)*/
会by month来转,不会by year来转晕掉
from salesByMonth pivot (sum(amount) for year in([2004],[2005]))t
參照
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
可能昨晚激情过度,脑袋有点发晕了
感谢liangCK的超音速回答!
结贴。