竖变横表-存储过程（万能型）

----------------------------------竖变横xxx -----------------------------------------------------
--总的来说 还有些问题待解决，请大家帮忙完善
-- exec 竖变横xxx 'job_desc','jobs'
--原理就是这个啦
select  
'Editor'= count(case job_desc when 'Editor' then job_desc  end ),
'yyyy'= count(case job_desc when 'yyyy' then  job_desc end ),
'Designer'= count( case job_desc when 'Designer' then  job_desc end)
from jobs
------------------------------------------
drop Proc 竖变横2
create Proc 竖变横2
(
@分组列名 nvarchar(130),
@分组表名 nvarchar(130)
)
as
declare @cursorSqlStr nvarchar(1000)
set @cursorSqlStr= 'declare cursorTest cursor for select distinct '+@分组列名+'  from '+ @分组表名
exec (@cursorSqlStr)
open cursorTest
declare @Test nvarchar(50),@Test2 nvarchar(50),@sqlStr nvarchar(4000)
set @sqlStr = ' select '
fetch  cursorTest  into @Test
while @@fetch_status =0
begin
set @Test2 =replace(@Test,' ','')
set @Test2 =replace(@Test2,'-','')
set @sqlStr = @sqlStr+''''+@Test2+' '''+ '=count(case '+@分组列名+' when '''+@Test+''' then '+@分组列名+'  end) '
fetch  cursorTest  into @Test
 set @sqlStr = @sqlStr+','
end
set @sqlStr =substring(@sqlStr,0,len(@sqlStr)-1)+  ' from  ' +@分组表名
close cursorTest
DEALLOCATE cursorTest
exec  (@sqlStr)

转载于:https://www.cnblogs.com/jacd/archive/2009/08/22/1551783.html