declare @yr int,@mth int,@logistics_code varchar(30),@fee_type varchar(50)

select @yr=2014,@mth=1,@logistics_code='All',@fee_type='All'

declare @index int,@from_date datetime,@to_date datetime,@strDateList varchar(180),@strSql varchar(1000)

select @index=1,@strDateList='',@from_date=cast((ltrim(@yr)+'-'+ltrim(@mth)+'-1') as datetime)

set @to_date=@from_date

while @index<=12

begin

    set @strDateList=@strDateList+'['+ltrim(year(@to_date))+'/'+ltrim(month(@to_date))+'],'

    set @to_date=dateadd(mm,1,@to_date)

    set @index=@index+1

end

create table #tbl

(

    ymth varchar(20),

    logistics_code varchar(30),

    fee money

)

insert into #tbl(ymth,logistics_code,fee)

select ltrim(yr)+'/'+ltrim(mth),logistics_code,sum(fee)

from tbl_lis_FeeList where status='A'

and

(

    (yr=year(@from_date) and mth>=month(@from_date))

    or (yr=year(@to_date) and mth<=month(@to_date))

)

and logistics_code=case when @logistics_code<>'All' then @logistics_code else logistics_code end

and fee_type=case when @fee_type<>'All' then @fee_type else fee_type end

group by ltrim(yr)+'/'+ltrim(mth),logistics_code

set @strDateList=substring(@strDateList,1,len(@strDateList)-1)

set @strSql='select b.customer_name,'+@strDateList+' from

(

    select logistics_code,'+@strDateList+' from #tbl pivot(sum(fee) for ymth in('+@strDateList+')) as t

) as temp

left join tbl_lis_LogisticsProvider b on temp.logistics_code=b.logistics_code'

exec (@strSql)

/*

select customer_name,temp.* from (

    select logistics_code,[2014/1],[2014/2],[2014/3],[2014/4],[2014/5],[2014/6],[2014/7],[2014/8],[2014/9],[2014/10],[2014/11],[2014/12]

    from #tbl

    pivot(sum(fee) for ymth in([2014/1],[2014/2],[2014/3],[2014/4],[2014/5],[2014/6],[2014/7],[2014/8],[2014/9],[2014/10],[2014/11],[2014/12])) as t

) as temp

left join tbl_lis_LogisticsProvider b on temp.logistics_code=b.logistics_code

*/

drop table #tbl

GO