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遞回查詢-範例資料庫

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 和 UNPIVOT、MSDN-使用 PIVOT 和 UNPIVOT

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-中間製品