Sql Server 2005 行转列的实现

来源：互联网发布：新郎礼服知乎编辑：程序博客网时间：2024/05/01 01:15

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)