SQLSERVER 动态生成列、合计行

wsh3166Sir / 2023-04-28 / 原文

/*

 
test110 '2023-04-26','2023-04-26','全部','901100569,901100570,901100571,901100572,901100573';
 
*/
ALTER  PROCEDURE [dbo].[test110]
    @StartDate  datetime,
    @EndDate  datetime ,
    @uname VARCHAR(50)='全部',
    @goodslist  VARCHAR(MAX) 
 
AS      
    set nocount on

 SET @EndDate=@EndDate+1
create table #t1(  
goodsno varchar(50)    
)   


create table #dtzd(  
column_name varchar(1150)   ,
ordinal_position int
 
)   

declare @sql varchar(MAX)
set @sql='insert into #t1(goodsno) select col='''+ replace(@goodslist,',',''' union all select ''')+''''
 PRINT @sql
exec (@sql)
 
 /*保存基础数据*/
select  a.num,a.md_uid,e.uname,b.sale_man,f.work_no,f.pname,b.gid,
 ( b.SUM_SALE ) jine, ( b.amount ) amount--,count(DISTINCT a.num) as rc
 into #raw1
from  td_outhead a with(nolock)
join td_outvoice b with(nolock) on(a.num=b.num)
 join tr_matecode c with(nolock) on(b.gid=c.gid)        
--join TD_PAY_INFO d with(nolock) on a.num=d.num       
join tr_unit  e with(nolock) on a.md_uid=e.uid    
join  #t1 X on X.goodsno=c.goodsno
LEFT JOIN tr_person f with(nolock) on b.sale_man= f.pid
where a.cw_date>=@StartDate and  a.cw_date <@EndDate                         
and( uname=@uname or @uname='全部')
-- group by a.md_uid,uname,b.sale_man,f.work_no,f.pname  
ORDER BY  a.md_uid,f.work_no  ;



 
 declare @sql1 varchar(max)
 set @sql1='SELECT a.md_uid,[uname] as 门店名称,[work_no] as 工号,[pname] 销售员 ,convert( DECIMAL(12,2),sum(a.jine)) as 总金额,
convert( DECIMAL(12,2),sum(a.amount)) as 总数量,count(DISTINCT a.num) as 单据数'

select @sql1=@sql1+' , convert( DECIMAL(12,2),sum(case gid when '''+ CONVERT(VARCHAR(10),b.gid) +''' then a.jine else 0 end) ) as  ['+
 CONVERT(VARCHAR(10),b.goodsno)+'/<br/>'+ b.comname +'/<br/>'+b.spec+'(金额)]'

+' , convert( DECIMAL(12,2),sum(case gid when '''+ CONVERT(VARCHAR(10),b.gid) +''' then a.amount else 0 end)) as  ['+
CONVERT(VARCHAR(10),b.goodsno)+'/<br/>'+ b.comname +'/<br/>'+b.spec+'(数量)]'
 
from #t1 a join TR_MATECODE b on a.goodsno=b.goodsno ORDER BY a.goodsno
set @sql1=@sql1+'  into #res
 from #raw1 a  
GROUP BY a.md_uid,[uname],[work_no],[pname]  order by a.md_uid,[work_no] ;

 
 insert into #dtzd(column_name,ordinal_position)
SELECT column_name,ordinal_position    --获取自动生成的列名字
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =
    object_name(
        object_id(''tempdb..#res''),
        (SELECT database_id FROM sys.databases WHERE name = ''tempdb''))
and ordinal_position>4   -- 第5列开始计算合计值
order by ordinal_position;
 

 
'

 
 exec(@sql1) ; /*执行一次给  #dtzd  赋值*/

set  @sql1=@sql1+' SELECT * from #res   union all select null, ''合计:'', null, null'  -- 第5列开始计算合计值

 select @sql1=@sql1+' ,sum(['+column_name+'])   '

from  #dtzd order by ordinal_position;

set  @sql1=@sql1+' from #res '
 PRINT(@sql1);
 exec(@sql1) ;


  --SELECT * from #dtzd 


/*--OLD


SELECT  md_uid,uname,sale_man,work_no,pname, CONVERT(DECIMAL(12,2),jine) as jine, CONVERT(float,amount) as amount,rc   from #res
union ALL
SELECT   NULL,'合计：',NULL,NULL,NULL, CONVERT(DECIMAL(12,2), sum(jine) ) as   jine, CONVERT(float, sum(amount) ) as   amount,
sum(rc) from #res
*/