--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


					
                        
 
                         

							
                             搜尋相關Tags的文章：
							[ T-SQL ] , [ SQL ] , [  動態欄位 ] , [ 交叉定位報表 ] , [ 動態交叉定位報表 ] , 


							
							 本篇文章發表於2007-08-21 16:17
                         
            

		    
			== 簽名檔 == 

			經驗是不斷累積來的,答案是Google來的XD