2012-04-27

MS SQL 遞回查詢

在許多Table中會有資料自我參照的情況,
例如部門代號表,會有組織階層的關係,
若想要列出部門的階層關係,從MS SQL Server 2005開始,
便支援遞回查詢功能,可以很簡單的表示出我們要的結果!
/*------------------------------------------------------------------*/
--建立測試資料
if (select OBJECT_ID('tempdb..#Dep')) IS NOT NULL DROP TABLE #Dep
CREATE TABLE #Dep (DEPNo varchar(5),
                    DEPNa nvarchar(20),
                    FDEPNo varchar(5))

INSERT INTO #Dep Values ('0100','管理部',NULL)
INSERT INTO #Dep Values ('0200','財務部',NULL)
INSERT INTO #Dep Values ('0210','會計課','0200')
INSERT INTO #Dep Values ('0211','應收組','0210')
INSERT INTO #Dep Values ('0212','應付組','0210')
INSERT INTO #Dep Values ('0213','稅務組','0210')
INSERT INTO #Dep Values ('0214','國際會計組','0210')
INSERT INTO #Dep Values ('0220','財務課','0200')
INSERT INTO #Dep Values ('0221','出納組','0220')
INSERT INTO #Dep Values ('0222','銀行相關組','0220')

/*------------------------------------------------------------------*/
WITH Dep_CTE (FDEPNo,DEPNo,DEPNa,DEP_PATH,DEP_LEVEL) AS
(    
    --頂層
    select FDEPNo,DEPNo,DEPNa,CAST(DEPNa as varchar(MAX)),0 AS DT_LEVEL
    from #Dep
    where FDEPNo is NULL
    
    union all    
    
    --成員
    select D.FDEPNo,D.DEPNo,D.DEPNa,CAST(DC.DEP_PATH+' > '+D.DEPNa as varchar(MAX)),DC.DEP_LEVEL+1
    from #Dep D INNER JOIN Dep_CTE DC on D.FDEPNo=DC.DEPNo
)

select * from Dep_CTE
/*------------------------------------------------------------------*/

參考自
MSDN-使用一般資料表運算式的遞迴查詢
Dot Robin Notes-CTE遞回查詢-範例資料庫

2012-04-21

MS SQL-UNPOVIT-橫向資料轉縱向

之前有寫過一篇PIVOT,可以將明細資料轉為二維匯總的橫向表示方式(如Excel資料),
如果想要將橫向多欄位資料轉為縱向表示(明細),則可使用UNPIVOT

--範例
--建立暫存表格
DECLARE @Table TABLE
( ProdNo varchar(3),
 RepQty int,
 ReaQty int,
 RecQty int)

--產生測試資料 
DECLARE @i INT
SET @i = 1
WHILE @i <= 3
BEGIN
 INSERT INTO @Table values (
  REPLICATE('0',(3-LEN(CAST(@i as varchar(3)))))+CAST(@i as varchar(3)),
  @i*(@i+1),
  @i*(@i+3),
  @i*(@i+5))
 SET @i = @i + 1
END

--資料轉換
select ProdNo,QtyType,Qty
from @Table
UNPIVOT
 (
 Qty FOR QtyType IN (RepQty,ReaQty,RecQty)
 ) PV

原始資料

執行結果


參考自:~楓花雪岳~[SQL] PIVOT 和 UNPIVOTMSDN-使用 PIVOT 和 UNPIVOT

2012-04-16

MS SQL Function 回傳資料表(Table)

透過View可以將一些常用但又較為複雜的查詢語法,
產生虛擬的資料表,簡化我們的查詢,
但在View並無法利用T-SQL彙整更新我們想要得到的查詢結果,
此時可使用Function並將回傳型態設為Table,來達到需求!
--建立測試用Function
CREATE FUNCTION [dbo].[FN_TEST]                 (@xGPNo varchar(5))--傳入值 RETURNS @TABLE TABLE (--設定傳入值格式                 DTNo varchar(5),                 DTNa Nvarchar(50),                 Item Nvarchar(100))--設定回傳值格式 AS BEGIN       --依據傳入GPNo將查詢結果INSERT至@TABLE     INSERT INTO @TABLE (DTNo,DTNa)     select DTNo,DTNa     from NOM     where GPNo=@xGPNo      --將@TABLE中DTNo及DTNa合併更新至Item欄位     UPDATE @TABLE     SET Item=DTNo+'-'+DTNa      RETURN--回傳 END --查詢 select * from FN_TEST('PI') --結果 DTNo    DTNa    Item MT    原材料    MT-原材料 PIP    在製品    PIP-在製品 WIP    中間製品    WIP-中間製品