樓主

Sdany

發送站內信

|
--SQL 動態欄位,交叉定位報表2 (第二版改良版) -- --MSSQL的交叉定位報表,局限於已知欄位才能定位, --如何將未知的資料,也能變成橫向的欄位呢? -- --本篇引用了「線上叢書」中的「交叉定位報表」方法修改而成 --以下,有寫自行修改者,是可以自己改的部份 --其它沒說明可改的,勿修改它(除非會改的自行參考) --改善上篇多迴圈、多暫存Table的問題 --當然,執行速度上也比較快 --並且多了「小計」、「總計」功能 --要能夠做動態欄位的小計、總計 --最主要是多了@fields及@fields2 --能夠活用它,就可以完成理想的報表 --By Sdany 2007.08.21
CREATE TABLE #pivot( Year SMALLINT, Quarter TINYINT, Amount DECIMAL(2,1) )
INSERT INTO #Pivot VALUES (1990, 1, 1.1) INSERT INTO #Pivot VALUES (1990, 2, 1.2) INSERT INTO #Pivot VALUES (1990, 3, 1.3) INSERT INTO #Pivot VALUES (1990, 4, 1.4) INSERT INTO #Pivot VALUES (1991, 1, 2.1) INSERT INTO #Pivot VALUES (1991, 2, 2.2) INSERT INTO #Pivot VALUES (1991, 3, 2.3) INSERT INTO #Pivot VALUES (1991, 4, 2.4)
INSERT INTO #Pivot VALUES (2006, 5, 5.5) INSERT INTO #Pivot VALUES (2007, 6, 3.3)
--獨立取出要產生的欄位,填入暫存Table select identity(int, 1, 1) as [pid],* into #t from ( --欄位、Table名稱,自行修改(限一個欄位) select distinct [quarter] from #pivot ) a
--交叉定位SQL語法字串 declare @sql varchar(5000) set @sql='' --單一欄位 declare @fields varchar(5000) set @fields='' --別名欄位 declare @fields2 varchar(5000) set @fields2='' --暫存資料 declare @t varchar(5000) set @t=''
----產生欄位字串 --欄位名稱,暫存變數 declare @field varchar(100) declare @i int set @i=1 while(not @i=0) begin --取得第一筆 pid set @i=isnull((select top 1 [pid] from #t),0) if not @i=0 begin --@field取得欄位名稱值 --[quarter],自行修改(限一個欄位) select top 1 @field=[quarter] from #t where [pid]=@i --decimal(6,1),改變欄位型別 --[quarter],自行修改(限一個欄位) --[amount],自行修改(限一個欄位),該欄位要填入的值 set @sql=@sql+',convert(decimal(6,1),sum(case when t.[quarter]='''+@field+''' then t.[amount] else 0 end)) as ['+@field+']' set @fields=@fields+'['+@field+']' set @fields2=@fields2+'v['+@field+']v'+'n['+@field+']n' delete #t where [pid]=@i end end drop table #t
--交叉定位報表 set @sql='select [year] '+@sql+' from #pivot as t group by t.year' exec (@sql)
--因為要做總計的計算,所以多做了一個暫存Table create table #t2 ( -- [pid] int identity(1,1) not null, [year] varchar(50) ) set @t=replace(@fields,'[','alter table #t2 add [') set @t=replace(@t,']','] decimal(6,1) default 0;') exec(@t) alter table #t2 add [小計] decimal(6,1) default 0
--交叉定位報表-加入小計 set @t=replace(@fields,'][',']+[') exec ('insert into #t2 select t.*,'+@t+' as 小計 from ('+@sql+') t') drop table #pivot select * from #t2
--交叉定位報表-加入小計-加入總計 set @t=replace(replace(@fields2,'v[','sum(['),']v',']) ') set @t=replace(replace(@t,'n[','['),']n','],') set @t=left(@t,len(@t)-1) --如果有多欄位的話,可以自行加入「群組加總」 exec('insert into #t2 select ''總計'','+@t+', sum([小計]) [小計] from #t2') select * from #t2 order by [year]
drop table #t2
== 簽名檔 ==
經驗是不斷累積來的,答案是Google來的XD
|