sqlserver2005中行转列的方法

1、数据准备

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

INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Jan', 789.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Feb', 389.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Mar', 8867.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Apr', 778.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','May', 78.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Jun', 9.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Jul', 987.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Aug', 866.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Sep', 7787.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Oct', 85576.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Nov', 855.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2004','Dec', 5878.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Jan', 7.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Feb', 6868.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Mar', 688.0000)
INSERT INTO sales.salesByMonth (year, month, amount)
VALUES('2005','Apr', 9897.0000)

 

2、语法

 

select year,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec
from  salesbymonth
pivot ( sum(amount) for month in (jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec)) as ourpivot