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

樓主

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

捐贈 VP 給 Sdany
參考範例:
--SQL 動態欄位,交叉定位報表(資料量大,不適合使用)
--
--MSSQL的交叉定位報表,局限於已知欄位才能定位,
--如何將未知的資料,也能變成橫向的欄位呢?
--
--本 Sample 可存放到「預存程序」中使用
--以下,有寫自行修改者,是可以自己改的部份
--其它沒說明可改的,勿修改它(除非會改的自行參考)
--By Sdany 2007.07.24

--CREATE PROCEDURE [dbo].[active_report] AS
--/* 本「CREATE TABLE #pivot」內容可刪除
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,並設定固定欄位
create table #tt (
[PID] int identity(1,1) not null
--以下固定欄位自行增加
,[Year] nvarchar(100)
)
--增加索引(暫時不確定是否有效果)
--索引、欄位名稱自行修改
create index [ix_tt] on [#tt]([year]) on [primary]

--獨立取出要產生的欄位,填入暫存Table
select identity(int, 1, 1) as [pid],* into #tt1 from (
--欄位、Table名稱,自行修改(限一個欄位)
select distinct [quarter] from #pivot
) a

--將資料行轉欄,填入 #tt 產生欄位
declare @field nvarchar(100)
declare @i int
set @i=1
while(not @i=0)
begin
set @i=isnull((select top 1 [pid] from #tt1),0)
if not @i=0
begin
--「[quarter]」欄位名稱,自行修改(限一個欄位)
set @field=isnull((select top 1 [quarter] from #tt1 where [pid]=@i),'')
--「decimal(2,1)」欄位型態,自行修改,加總項目,預設為0 (勿用null)
exec ('alter table #tt add ['+@field+'] decimal(2,1) default 0')
delete #tt1 where [pid]=@i
end
end
drop table #tt1

--取出內容
select identity(int, 1, 1) as [pid],* into #tt2 from (
--「#pivot」Table名稱,自行修改
--若欄位不多,用*即可,若欄位很多,請自行修改要取出資料的部份
--主要是群組(year)、欄位、值(三個部份)
select * from #pivot
) a
--同「CREATE TABLE #pivot」一並刪除
drop table #pivot

--將資料填入更新
declare @sfield nvarchar(100)
set @i=1
while(not @i=0)
begin
set @i=isnull((select top 1 [pid] from #tt2),0)
if not @i=0
begin
--「[quarter]」欄位名稱,自行修改(限一個欄位)
set @field=(select [quarter] from #tt2 where [pid]=@i)
--「[year]」欄位名稱,自行修改(各限一個欄位)
set @sfield=isnull((select [year] from #tt where [year]=(select [year] from #tt2 where [pid]=@i)),'')
if @sfield=''
begin
--※新增資料欄位數必須對應
--「[year]」欄位名稱,自行修改(增加固定欄位)
exec ('insert into #tt ([year],['+@field+'])
(select [year],[amount] from #tt2 where [pid]='+@i+')')
--「[year],[amount]」欄位名稱,自行修改
end
else
begin
--「[amount]」欄位名稱,自行修改(限一個欄位)
exec ('update #tt set ['+@field+']=t.['+@field+']+[amount]
from #tt t join (select * from #tt2 where [pid]='+@i+') a on t.[year]=a.[year]')
--「[year]」欄位名稱,自行修改(對應欄位)
end
delete #tt2 where [pid]=@i
end
end
drop table #tt2
--資料輸出
select * from #tt
drop table #tt

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

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