台灣最大程式設計社群網站
線上人數
898
 
會員總數:245214
討論主題:189087
歡迎您免費加入會員
討論區列表 >> 專欄文章 >> SQL 動態欄位,交叉定位報表2 (第二版改良版)
[]  
[我要回覆]
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
SQL 動態欄位,交叉定位報表2 (第二版改良版)
價值 : 0 QP  點閱數:2221 回應數:0

樓主

Sdany
中級顧問
45676 77
16637 5367
發送站內信

捐贈 VP 給 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


搜尋相關Tags的文章: [ T-SQL ] , [ SQL ] , [ 動態欄位 ] , [ 交叉定位報表 ] , [ 動態交叉定位報表 ] ,
本篇文章發表於2007-08-21 16:17
== 簽名檔 ==
經驗是不斷累積來的,答案是Google來的XD
別忘捐VP感謝幫助你的人 新手會員瞧一瞧
目前尚無任何回覆
   

回覆
如要回應,請先登入.