sql行列转换

Create table  #T  (Class varchar(2),CallDate datetime, CallCount int)
insert into #T select '1','2005-8-8',40
union all select '1','2005-8-7',6
union all select '2','2005-8-8',77
union all select '3','2005-8-9',33
union all select '3','2005-8-8',9
union all select '3','2005-8-7',21
--动态SQL
declare @s varchar(8000)
set @s='select CallDate '
select @s=@s+',[CallCount'+Class+']=max(case when Class='''+Class+''' then CallCount else 0 end)'
from #T 
group by Class

set @s=@s+' from #T group by CallDate order by CallDate desc '
exec(@s)

drop table #T

 

完全行列转换

Create table #ta(xm varchar(10),v int ,haha int , pos int)
Go
Insert into #ta
 select '?目1',15,22,1 union all


 select '?目2',67,23,1 union all


 select '?目3',8,24,1 union all


 select 'xm4',3,25,1 


Go
--Start
declare @s varchar(1000)
select @s = isnull(@s +',','')+ '['+xm+']=  '+convert(nvarchar(10),v)
from (select  xm,v from #ta) a

--exec('select '+@s + ' from ta  group by pos')
set @s='select '+@s + ' from #ta  group by pos'
--select @s

declare @s1 varchar(1000)
select @s1 = isnull(@s1 +' ,','')+ ' ['+xm+']= '+ convert(nvarchar(10),haha)
from (select  xm, haha from #ta) a

set @s1='select '+@s1 + ' from #ta group by pos'
--exec('select '+@s1 + ' from ta group by pos')

exec('select * from  ( '+@s+' union all  '+@s1 + ' ) dd ')
    Drop table #ta