sqlserver列转行

        
--exec up_工单类型统计 '1,2,3,5,6,136,138,142,143,144,145,137,141,146,139,140,147,148,149,150,151,152,4,7,8,9,153,155,156,157,158,159,160,161,154,135,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59','2016-08-24','2019-08-24'        
CREATE proc up_工单类型统计        
@Department_id varchar(max),        
@BeginDt varchar(50),        
@EndDt varchar(50)        
as        
declare @sql varchar(max);        
declare @str varchar(max);      
declare @str1 varchar(max);        
declare @str2 varchar(max);                              
set @str='' ;      
set @str1 = '';       
set @str2 = '';                             
declare m_cursor cursor scroll for                              
select ID from vw_GD_type_basic                               
open m_cursor                              
declare   @Address varchar(50)                              
fetch next from m_cursor into @Address                              
while @@FETCH_STATUS=0                              
begin                              
    set @str=@str+'['+@Address+']'+',';         
    set @str1 = @str1+'isnull(b.['+@Address+'],0) ['+@Address+'],';           
    set @str2 = @str2+ 'SUM(['+@Address+']) ['+@Address+'],'                      
    fetch next from m_cursor into @Address                              
end                              
close m_cursor                              
DEALLOCATE m_cursor                              
set @str=substring(@str,0,len(@str));      
set @str1=substring(@str1,0,len(@str1));      
set @str2=substring(@str2,0,len(@str2));      
set @sql ='        
        
select * into #tmp from vw_GD_main_public where CreateDate>'''+@BeginDt+''' and CreateDate<'''+@EndDt+''' and Department_id in ('+@Department_id+')         
        
select Department_id,Department_name,COUNT(*) zs,        
SUM(case State when 4 then 1 else 0 end) ywc,        
SUM(case State when 4 then 0 else 1 end) wwc,         
SUM(case when State<>4 and ifhb=1 then 1 else 0 end) wwchb,      
SUM(case ifhf when 1 then 1 else 0 end) yhf,      
SUM(case when ManYiDu=4 or ManYiDu=5 then 1 else 0 end) myl      
into #tmp1        
from         
#tmp        
group by Department_id,Department_name        
      
        
        
select * into #tmp2        
from          
(        
select Department_id,Department_name,Gd_type_id,COUNT(*) count        
from         
#tmp        
group by Department_id,Department_name,Gd_type_id        
) t        
pivot ( sum(count) for t.Gd_type_id in ('+@str+')) as ourpivot        
        
select d.Department_name ParentName,c.ParentNo,a.Department_name,a.Department_id,a.zs,a.ywc,a.wwc,a.wwchb,a.yhf,a.myl,'+@str1+' into #tmp3 from         
#tmp1 a inner join         
#tmp2 b on a.Department_id=b.Department_id left join         
Sys_department c on a.Department_id=c.ID left join         
Sys_department d on c.ParentNo=d.ID        
      
select ParentName,ParentNo,''小计'' Department_name,9999 Department_id,SUM(zs) zs,SUM(ywc) ywc,SUM(wwc) wwc,SUM(wwchb) wwchb,SUM(yhf) yhf,SUM(myl) myl,'+@str2+' into #tmp4 from #tmp3 group by ParentName,ParentNo      
      
select ''全部'' ParentName,9999 ParentNo,''合计'' Department_name,9999 Department_id,SUM(zs) zs,SUM(ywc) ywc,SUM(wwc) wwc,SUM(wwchb) wwchb,SUM(yhf) yhf,SUM(myl) myl,'+@str2+' into #tmp5 from #tmp3       
      
      
      
select * from (      
select * from #tmp3       
union all      
select * from #tmp4      
union all      
select * from #tmp5      
) aa order by ParentNo,Department_id      
      
drop table #tmp      
drop table #tmp1      
drop table #tmp2      
drop table #tmp3      
drop table #tmp4      
drop table #tmp5      
      
';        
exec (@sql);