参考: create table tb(名稱 varchar(10),數量 numeric(10),類型 varchar(5)) Insert into tb select 'L001','1','A' union all select 'L001','2','B' union all select 'L002','5','C' union all select 'L003','6','D' union all select 'L004','9','A' union all select 'L004','5','D'select * from tbdeclare @sql varchar(1000) set @sql='' select @sql=@sql+',['+max(類型)+']=sum(case 類型 when '''+max(類型)+''' then 數量 else 0 end)' from tb group by 類型 print @sqlexec('select 名稱'+@sql+' from tb group by 名稱') --結果 名稱 A B C D --------------------------------------- L001 1 2 0 0 L002 0 0 5 0 L003 0 0 0 6 L004 9 0 0 5
固定的行列互换用case when 语句。
create table #table1 ( A varchar(10), B int ) insert into #table1 select 'f1',2 union all select 'f2',3 union all select 'f3',2select sum(case when A='f1' then 3 else 0 end) 'f1', sum(case when A='f2' then 3 else 0 end) 'f2', sum(case when A='f3' then 2 else 0 end) 'f3' from #table1f1 f2 f3 ----------- ----------- ----------- 3 3 2(1 行受影响)
下面是动态的 declare @str varchar(1000) set @str='select ' select @str=@str+'sum(case when A= '''+A+''' then B else 0 end) ['+A+'],' from (select distinct(A) from #table1) T set @str=left(@str,len(@str)-1) select @str=@str+'from #table1' exec(@str)f1 f2 f3 ----------- ----------- ----------- 2 3 2(1 行受影响)
create table tb(名稱 varchar(10),數量 numeric(10),類型 varchar(5))
Insert into tb
select 'L001','1','A'
union all select 'L001','2','B'
union all select 'L002','5','C'
union all select 'L003','6','D'
union all select 'L004','9','A'
union all select 'L004','5','D'select * from tbdeclare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(類型)+']=sum(case 類型 when '''+max(類型)+''' then 數量 else 0 end)'
from tb group by 類型
print @sqlexec('select 名稱'+@sql+' from tb group by 名稱')
--結果
名稱 A B C D
---------------------------------------
L001 1 2 0 0
L002 0 0 5 0
L003 0 0 0 6
L004 9 0 0 5
(
A varchar(10),
B int
)
insert into #table1 select 'f1',2
union all select 'f2',3
union all select 'f3',2select sum(case when A='f1' then 3 else 0 end) 'f1',
sum(case when A='f2' then 3 else 0 end) 'f2',
sum(case when A='f3' then 2 else 0 end) 'f3'
from #table1f1 f2 f3
----------- ----------- -----------
3 3 2(1 行受影响)
declare @str varchar(1000)
set @str='select '
select @str=@str+'sum(case when A= '''+A+''' then B else 0 end) ['+A+'],'
from (select distinct(A) from #table1) T
set @str=left(@str,len(@str)-1)
select @str=@str+'from #table1'
exec(@str)f1 f2 f3
----------- ----------- -----------
2 3 2(1 行受影响)
http://shenliang1985.blog.163.com/blog/static/29083805200922211403868/edit/