亂馬客

前言

上個星期跟同事Code Review時，發現一支Store Procedure裡面要做Cross tab queries，居然是從SQL 2000使用到現在專門在做Cross Tab的Store Procedure來處理!

SQL 2005早就有PIVOT可以來處理Cross tab ! 

使用說明

以下使用一個員工加班倍率資料的範例，來一步一步說明如何使用PIVOT，

1.建立測試資料

CREATE TABLE PIVOT_TEST
(
    EMP_NAME NVARCHAR(10)
    , OT_RATE DECIMAL(8, 2)
    , OT_HOURS DECIMAL(8, 2)
);

--輸入資料
INSERT INTO PIVOT_TEST VALUES('亂馬客', 1.00, 1.5);
INSERT INTO PIVOT_TEST VALUES('亂馬客', 1.33, 8);
INSERT INTO PIVOT_TEST VALUES('亂馬客', 1.33, 1);
INSERT INTO PIVOT_TEST VALUES('郭小玉', 1.33, 8);
INSERT INTO PIVOT_TEST VALUES('打死釘', 1.00, 2);
INSERT INTO PIVOT_TEST VALUES('打死釘', 1.33, 1);
INSERT INTO PIVOT_TEST VALUES('打死釘', 1.33, 1.5);
INSERT INTO PIVOT_TEST VALUES('打死釘', 1.66, 4);

SELECT * 
FROM PIVOT_TEST
ORDER BY EMP_NAME;

--GROUP
SELECT EMP_NAME, '倍率-' + RTRIM(OT_RATE) AS OT_RATE_NAME,  SUM(OT_HOURS) AS SUM_OF_OT_HOURS   
FROM PIVOT_TEST 
GROUP BY EMP_NAME, '倍率-' + RTRIM(OT_RATE);

 

2.使用PIVOT(將要Cross Tab的欄位寫在PIVOT之中)

SELECT *
FROM 
(SELECT EMP_NAME, '倍率-' + RTRIM(OT_RATE) AS OT_RATE_NAME,  SUM(OT_HOURS) AS SUM_OF_OT_HOURS   
FROM PIVOT_TEST 
GROUP BY EMP_NAME, '倍率-' + RTRIM(OT_RATE)
) AS X
PIVOT
(
    SUM(SUM_OF_OT_HOURS)
    FOR OT_RATE_NAME IN 
    ([倍率-1.00], [倍率-1.33], [倍率-1.66])
) AS PVT
ORDER BY EMP_NAME;

 

3.動態使用欄位(因為倍率資料有可能會不同，所以取代原本寫死的「[倍率-1.00], [倍率-1.33], [倍率-1.66]」)

DECLARE @in_columns NVARCHAR (MAX)
SELECT @in_columns = COALESCE (@in_columns + ',[' +  '倍率-' +  RTRIM(T.OT_RATE) + ']', '[' + '倍率-' +  RTRIM(T.OT_RATE) + ']')
FROM  (SELECT DISTINCT OT_RATE FROM PIVOT_TEST) T
ORDER BY T.OT_RATE
PRINT @in_columns

EXECUTE('SELECT *
FROM 
(SELECT EMP_NAME, ''倍率-'' + RTRIM(OT_RATE) AS OT_RATE_NAME,  SUM(OT_HOURS) AS SUM_OF_OT_HOURS   
FROM PIVOT_TEST 
GROUP BY EMP_NAME, ''倍率-'' + RTRIM(OT_RATE)
) AS X
PIVOT
(
    SUM(SUM_OF_OT_HOURS)
    FOR OT_RATE_NAME IN 
    (' +  @in_columns + ')
) AS PVT
ORDER BY EMP_NAME');

 

4.處理NULL資料(因為資料有些為NULL，所以使用ISNULL來處理)

DECLARE @in_columns NVARCHAR (MAX)
SELECT @in_columns = COALESCE (@in_columns + ',[' +  '倍率-' +  RTRIM(T.OT_RATE) + ']', '[' + '倍率-' +  RTRIM(T.OT_RATE) + ']')
FROM  (SELECT DISTINCT OT_RATE FROM PIVOT_TEST) T

PRINT @in_columns;

--SELECT的欄位
DECLARE @select_columns NVARCHAR (MAX) = '';
SELECT @select_columns = @select_columns +  ',ISNULL([' +  '倍率-' +  RTRIM(T.OT_RATE) + '],0) AS [倍率-' +  RTRIM(T.OT_RATE) + ']' 
FROM  (SELECT DISTINCT OT_RATE FROM PIVOT_TEST) T
ORDER BY T.OT_RATE;
PRINT @select_columns;

EXECUTE('SELECT EMP_NAME' + @select_columns + '
FROM 
(SELECT EMP_NAME, ''倍率-'' + RTRIM(OT_RATE) AS OT_RATE_NAME,  SUM(OT_HOURS) AS SUM_OF_OT_HOURS   
FROM PIVOT_TEST 
GROUP BY EMP_NAME, ''倍率-'' + RTRIM(OT_RATE)
) AS X
PIVOT
(
    SUM(SUM_OF_OT_HOURS)
    FOR OT_RATE_NAME IN 
    (' +  @in_columns + ')
) AS PVT
ORDER BY EMP_NAME');

 

透過以上範例來說明如何使用PIVOT，希望對大家有幫助!

Example:

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^