文章来源: http://ningoo.itpub.net/post/2149/281485
创建测试表,插入测试数据
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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 输出结果:
行列转换:
select
id,name,
[
1
]
,
[
2
]
,
[
3
]
,
[
4
]
from
test
pivot
(
sum (profile) for quarter in ( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
)
as pvt
pivot
(
sum (profile) for quarter in ( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] )
)
as pvt
转换后的结果:
假设需要转换的列不固定,可以使用下面方法:
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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')