动态SQL怎么加1横向汇总列_Sql Server

动态SQL如何加1横向汇总列
declare @sql varchar(8000)
select
  @sql=isnull(@sql+',','')
  +'sum(case when DAY(createdate)='+ltrim(abc)+' then 1 else 0 end) as ['+ltrim(abc)+']'
from
(select distinct DAY(createdate) abc from ts_cttsp_faultworkorder
where createdate>='2014-01-01' and createdate<='2014-10-01' and DAY(createdate)>=1 and DAY(createdate)<=4) t
exec ('select yxcentername,Site_name,machinName,'+@sql+' from ts_cttsp_faultworkorder group by yxcentername,Site_name,machinName')

执行结果如下（3、4是动态生成的日期列）：
yxcentername Site_name   machinName 3 4
南山            附城    附城            1 2

需要的结果如下：
yxcentername Site_name   machinName 3 4     合计
南山            附城    附城            1 2        3

------解决思路----------------------
呃，我之前有个地方没注意
应该是用',ISNULL(SUM(CASE WHEN DAY(createdate)<=4 THEN 1 END),0)[合计]'
而不是',ISNULL(SUM(1),0)[合计]'
我猜，你应该是要查时间在2014-01-01到2014-10-01期间，每个月前四天的情况
我把SQL修改成如下，如有什么不妥的地方再贴上来吧

DECLARE @SQL VARCHAR(8000)
DECLARE @INDEX INT
SET @SQL=''
SET @INDEX=1
WHILE @INDEX<=4
BEGIN
	SET @SQL=@SQL+',ISNULL(SUM(CASE WHEN DAY(createdate)='+CAST(@INDEX AS VARCHAR(10))+' THEN 1 END),0)['+CAST(@INDEX AS VARCHAR(10))+']'
	SET @INDEX=@INDEX+1
END
SET @SQL=@SQL+',ISNULL(SUM(CASE WHEN DAY(createdate)<=4 THEN 1 END),0)[合计]'
EXEC ('SELECT yxcentername,Site_name,machinName'+@sql+' FROM ts_cttsp_faultworkorder WHERE createdate>=''2014-01-01'' AND createdate<=''2014-10-01'' GROUP BY yxcentername,Site_name,machinName')

------解决思路----------------------

declare @sql varchar(8000)
select
  @sql=isnull(@sql+',','')
  +'sum(case when DAY(createdate)='+ltrim(abc)+' then 1 else 0 end) as ['+ltrim(abc)+']'
from
(select distinct DAY(createdate) abc from ts_cttsp_faultworkorder 
where createdate>='2014-01-01' and createdate<='2014-10-01' and DAY(createdate)>=1 and DAY(createdate)<=4) t

/*-----------------增加的内容-------------------*/
declare @sql1 varchar(8000)
select
  @sql1=isnull(@sql1+'+','')
  +'sum(case when DAY(createdate)='+ltrim(abc)+' then 1 else 0 end)'
from
(select distinct DAY(createdate) abc from ts_cttsp_faultworkorder 
where createdate>='2014-01-01' and createdate<='2014-10-01' and DAY(createdate)>=1 and DAY(createdate)<=4) t
set @sql1 = @sql1 + ' as 合计' 
/*------------------增加的内容--------------------*/
exec ('select yxcentername,Site_name,machinName,'+@sql+','+@sql1+' from ts_cttsp_faultworkorder group by yxcentername,Site_name,machinName')

就是把','换成'+'就可以了啊多个动态语句，你试试