นพ ภิญโญวาณิชกะ

เขียน MSSQL store procedure เพื่อทำ Crosstab หรือ Pivot Table

วันก่อน มีโอกาสต้องเขียน การแสดงผลข้อมูลเป็นแบบ แนวนอน หรือที่เค้าเรียกกันว่า Crosstab แต่เนื่องด้วย ฐานข้อมูลที่ใช้ มันเป็น MSSQL 2000 มันไม่มีพวก Pivot Table มาให้ (Access ยังมีเลย) เลยต้องหาเอาเอง ไปเจอเว็บนึงเค้า เขียนเป็น Store Procedure ไว้ เอามาใช้ Work เลยทีเดียว ตอบโจทย์ได้เลย

ตัวอย่างของ Data

Year Region Income
Y2007 North 20
Y2007 South 10
Y2007 East 30
Y2006 North 10

Y2006 South 10

Y2006 East 10

เมื่อทำ Crosstab แล้วจะได้แบบนี้
ํ
Year North South East
Y2006 10 10 10
Y2007 20 10 30

อ้าวแล้วทำยังไงให้ได้แบบนี้ล่ะ มาดูวิธีการกันเลยครับ

เขียน Store Proc ดังนี้

CREATE     PROC sp_CrossTab
  @table       AS sysname,        -- Table to crosstab
  @onrows      AS nvarchar(128),  -- Grouping key values (on rows)
  @onrowsalias AS sysname = NULL, -- Alias for grouping column
  @oncols      AS nvarchar(128),  -- Destination columns (on columns)
  @sumcol      AS sysname = NULL  -- Data cells
AS

DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @sql =
  'SELECT' + @NEWLINE +
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table

EXEC (@keyssql)

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS D' + @key

  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

 PRINT @sql  + @NEWLINE -- For debug
EXEC (@sql)

ใน Database Master (เพื่อเรียกใ้ช้ได้ทุก Database แบบง่ายๆหน่อย)

วิธีเรียกใช้งานก็

EXEC sp_crosstab
@table = ‘ชื่อ Table ที่ต้องการสร้าง Crosstab จากมัน’,
@onrows = ‘Field ที่ต้องการเอามาเป็น Row’,
@oncols = ‘Field ที่ต้องการเอามาเป็น Column’,
@sumcol = ‘Field ที่ต้องการเอามาเป็น จำนวนรวม’

แค่นี้เราก็สามารถสร้าง ตาราง Crosstab ได้อย่างง่ายๆ แล้วครับ