Sql Server 2005 行转列的实现

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

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

原始数据是

要达到的上的是

实现方法：

declare @Str nvarchar(max)
set @str='select Year'
select @str=@str+',['+month+']' from sales group by month
set @str=@str+' FROM (
 SELECT year, amount, month
 FROM sales ) AS salesByMonth
PIVOT ( sum(amount) FOR month IN
  ('
select @str=@str+'['+month+'],' from sales group by month
set @str=left(@str,Len(@str)-1)
set @str=@str+ ')) AS ourPivot
ORDER BY Year'

exec(@str)