-----------------------列から行まで---------------------------------------------------------------------------------------------------------
select
部門CD
,部門名
,sum(case (伝票日付%100) when 1 then 残高 else 0 end) as '1日'
,sum(case (伝票日付%100) when 2 then 残高 else 0 end) as '2日'
,sum(case (伝票日付%100) when 3 then 残高 else 0 end) as '3日'
,sum(case (伝票日付%100) when 4 then 残高 else 0 end) as '4日'
,sum(case (伝票日付%100) when 5 then 残高 else 0 end) as '5日'
,sum(case (伝票日付%100) when 6 then 残高 else 0 end) as '6日'
,sum(case (伝票日付%100) when 7 then 残高 else 0 end) as '7日'
,sum(case (伝票日付%100) when 8 then 残高 else 0 end) as '8日'
,sum(case (伝票日付%100) when 9 then 残高 else 0 end) as '9日'
,sum(case (伝票日付%100) when 10 then 残高 else 0 end) as '10日'
,sum(case (伝票日付%100) when 11 then 残高 else 0 end) as '11日'
,sum(case (伝票日付%100) when 12 then 残高 else 0 end) as '12日'
,sum(case (伝票日付%100) when 13 then 残高 else 0 end) as '13日'
,sum(case (伝票日付%100) when 14 then 残高 else 0 end) as '14日'
,sum(case (伝票日付%100) when 15 then 残高 else 0 end) as '15日'
,sum(case (伝票日付%100) when 16 then 残高 else 0 end) as '16日'
,sum(case (伝票日付%100) when 17 then 残高 else 0 end) as '17日'
,sum(case (伝票日付%100) when 18 then 残高 else 0 end) as '18日'
,sum(case (伝票日付%100) when 19 then 残高 else 0 end) as '19日'
,sum(case (伝票日付%100) when 20 then 残高 else 0 end) as '20日'
,sum(case (伝票日付%100) when 21 then 残高 else 0 end) as '21日'
,sum(case (伝票日付%100) when 22 then 残高 else 0 end) as '22日'
,sum(case (伝票日付%100) when 23 then 残高 else 0 end) as '23日'
,sum(case (伝票日付%100) when 24 then 残高 else 0 end) as '24日'
,sum(case (伝票日付%100) when 25 then 残高 else 0 end) as '25日'
,sum(case (伝票日付%100) when 26 then 残高 else 0 end) as '26日'
,sum(case (伝票日付%100) when 27 then 残高 else 0 end) as '27日'
,sum(case (伝票日付%100) when 28 then 残高 else 0 end) as '28日'
,sum(case (伝票日付%100) when 29 then 残高 else 0 end) as '29日'
,sum(case (伝票日付%100) when 30 then 残高 else 0 end) as '30日'
,sum(case (伝票日付%100) when 31 then 残高 else 0 end) as '31日'
into #tt23
from #tt22
group by
部門CD
,部門名
order by 部門CD
------------------------------------SQL文の実行------------------------------------------------------------------------------------------
declare @Count int
set @Count=(select count(*) from #KMCnt)
declare @S varchar(8000)
declare @i as int
set @i=1
declare @B varchar(50)
set @B=''
set @S=''
while(@i<=@Count)
begin
set @B=(select section from #KMCnt where id_sum=@i)
set @S=@S+
'
,sum(case (集計科目名) when'''+ @B +''' then Blance else 0 end) as '''+@B+''''
set @i=@i+1
end
--@SQLの長さが8000を超えるなら、エラーが発生することができます。
--計算の結果表の整理--------
delete from GetBranchItemData
set @S = 'insert into GetBranchItemData select 集計部門CD,集計部門名 '+@S
+' from #blance group by 集計部門CD,集計部門名 order by 集計部門CD'
exec (@S)