declare @a varchar(1000)
set @a='select name '
select @a=@a+',sum(case when a='''+a+''' then b end)'+' as '+ a ----這裡sum改為min或max結果一樣
from (select distinct a from TableA)T1
set @a=@a+' from TableA group by name'
--exec(@a)
print (@a)
select name ,sum(case when a='andy ' then b end) as andy ,sum(case when a='fenlam' then b end) as fenlam from TableA group by name
(我的測試表是 Tablea(name ,a,b)以a開始行列轉換
set @a='select name '
select @a=@a+',sum(case when a='''+a+''' then b end)'+' as '+ a ----這裡sum改為min或max結果一樣
from (select distinct a from TableA)T1
set @a=@a+' from TableA group by name'
--exec(@a)
print (@a)
select name ,sum(case when a='andy ' then b end) as andy ,sum(case when a='fenlam' then b end) as fenlam from TableA group by name
(我的測試表是 Tablea(name ,a,b)以a開始行列轉換
(
[日期] varchar(10),
[名字] varchar(10),
[价格] int
)
insert A
select '20050505','a',2 union
select '20050506','b',3 union
select '20050507','c',5 union
select '20050508','d',2 union
select '20050509','e',2 --测试
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+[名字]+']=sum(case when [名字]='''+[名字]+''' then [价格] else 0 end)'
from A
group by [名字]
set @sql='select [日期]'+@sql+' from A group by [日期]'
--print @sql
exec(@sql)--删除测试环境
drop table A--结果
/*
日期 a b c d e
---------- ----------- ----------- ----------- ----------- ----
20050505 2 0 0 0 0
20050506 0 3 0 0 0
20050507 0 0 5 0 0
20050508 0 0 0 2 0
20050509 0 0 0 0 2
*/