----------------------------------竖变横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)