http://ningoo.itpub.net/post/2149/281485
创建测试表,插入测试数据
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select * from test 输出结果:
![](https://images.cnblogs.com/cnblogs_com/7788/pvt1.jpg)
行列转换:
select id,name,[1],[2],[3],[4] from test
pivot
(
sum(profile) for quarter in ([1],[2],[3],[4])
)
as pvt
转换后的结果:
![](https://images.cnblogs.com/cnblogs_com/7788/pvt2.jpg)
假设需要转换的列不固定,可以使用下面方法:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
DECLARE @S NVARCHAR(1000)
SELECT @S = ISNULL(@S + ',','') + '[' + cast(quarter as nvarchar(20)) + ']' FROM ( SELECT DISTINCT [quarter] FROM test) a
EXEC('SELECT id,name,' + @S + 'FROM TEST '
+ 'pivot
(
SUM(profile) for quarter in (' + @S + ')
)
as pvt')