Sql Server 2005 行转列的实现

最新推荐文章于 2018-05-09 10:46:00 发布

原创最新推荐文章于 2018-05-09 10:46:00 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #insert #table

T-SQL 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种使用SQL进行数据透视的方法，通过创建销售表格并利用动态SQL实现按月份汇总销售额的功能，适用于需要定期生成报告的场景。

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)