การทำ Pivot Table กลาง Query ด้วย SQL Server 2005

การทำ Pivot Table กลาง Query ด้วย SQL Server 2005

09/11/2009 — panya
credit http://khunpanya.wordpress.com/2009/11/09/pivot-unpivot/

Pivot Table หมายถึงลักษณะของตารางที่แสดงข้อมูลในเชิงวิเคราะห์ (Analytic) ไม่ใช่ข้อมูลดิบแบบ Transaction
จุดเด่นที่สังเกตได้ก็คือคอลัมน์ของ Pivot Table จะไม่ตายตัว แต่จะได้มาจากตัวเนื้อข้อมูล ต่างกับ Table ธรรมดา
ที่มักจะ fix คอลัมน์ไว้แล้วว่าประกอบด้วยข้อมูลอะไรบ้าง และตัวเลขใน Pivot Table ก็มักจะเป็นค่าที่สามารถวัดผล (Measure) ได้
โดยมากมักจะเป็นค่าที่เกิดจาก Aggregate function อย่างเช่น Sum, Avg, Max, Min เป็นต้น

Pivot Table มักจะถูกใช้นำเสนอข้อมูลให้แก่ผู้บริหาร เนื่องจากสามารถให้ Information ได้แทน Data
กล่าวคือมันสามารถแสดงภาพรวมหรือแนวโน้มของข้อมูลได้โดยไม่จำเป็นต้องดูรายละเอียดของข้อมูลทั้งหมด
เพราะผู้บริหารคงจะสนใจแค่เพียงว่าไตรมาสนี้ยอดขายในภูมิภาคไหนมีค่ารวมเท่าไหร่ แต่คงไม่มานั่งดูว่าขายของให้ลูกค้าชื่ออะไรไปบ้าง
เพื่อที่จะตอบโจทย์ในทางบริหารนี้ บางครั้งเราจำเป็นต้องหมุนแกนของตาราง พลิกเอาบางมิติของข้อมูลไปในแนวนอนบ้างแนวตั้งบ้าง
การหมุนแกนของตารางนี่เองเป็นที่มาของชื่อ Pivot Table (Pivot หมายถึงหมุดที่ใช้สำหรับตรึงแกนหมุน)

ตัวอย่างข้อมูลลักษณะนี้คือ Transaction ของการขายของ (ข้อมูลที่นำมาแสดงตัวอย่างนี้นำมาจากฐานข้อมูล AdventureWorks
จัดทำโดย Microsoft เพื่อสาธิตการทำงานของ SQL Server สามารถ Download ไปลองเล่นดูได้ที่นี่)

เครื่องมือที่เราจะสามารถใช้ทำ Pivot Table ได้อย่างหนึ่งก็คือใช้ Excel
จากตัวอย่างข้างต้นนี้ ถ้าเราทำการ “Pivot” ข้อมูล โดยเอา Month เป็นข้อมูลแนวตั้ง
แล้วหมุนเอา Country ออกไปในแนวขวาง ส่วนตัวเลขในตาราง ให้แสดงยอดขายรวมในแต่ละเดือน/ประเทศนั้นๆ
ก็จะได้ผลลัพธ์หน้าตาประมาณนี้

บางทีเราอาจจะใส่รายละเอียดลงไปทั้งในมิติของเวลา โดยเพิ่มระดับของ Quarter เข้าไป
และในมิติของพื้นที่นั้น ให้เพิ่มระดับของ City เข้าไป ก็จะได้ข้อมูลที่ละเอียดขึ้นดังนี้

จะเห็นว่าที่จริงเราไม่ควรลงไปในรายละเอียดว่า มีการขายเกิดขึ้นเมื่อวันไหน ให้แก่ลูกค้าเจ้าไหน
แต่ควรจะเน้นการ Summmarize ข้อมูลมากกว่า เพราะยิ่งลง detail มาก จะยิ่งดูตารางไม่รู้เรื่อง
ทั้งหมดนี้คือคุณลักษณะคร่าวๆ ของ Pivot Table

คำสั่ง Pivot ใน SQL Server
ดังที่ได้กล่าวมาแล้วว่าเราสามารถใช้ Excel ในการทำ Pivot Table จากข้อมูลดิบได้
หรือเราสามารถใช้พวก Report Tool เช่น Crystal Report ทำ CrossTab
หรือใช้ ReportViewer ทำ Matrix ก็ได้เหมือนกัน
แต่ในที่นี้เราจะพูดถึงการทำ Pivot ข้อมูลในชั้นของ Database เลยทีเดียว
ตั้งแต่ SQL Server 2005 เป็นต้นมา จะมีคำสั่ง pivot และ unpivot
ช่วยให้เราสามารถจำลองหน้าตาของ pivot table ขึ้นได้ใน query เดียว
ที่ผมใช้คำว่า “จำลอง” เพราะว่า เราไม่สามารถที่จะทำ dynamic column ได้จริงๆ
ต้องมีการกำหนดคอลัมน์ไว้ล่วงหน้า สิ่งที่เราสามารถทำได้ก็เพียงแค่การหมุนข้อมูล
จากแนวนอนเป็นแนวตั้งหรือแนวตั้งเป็นแนวนอนเท่านั้น
และผลลัพธ์ที่ได้ ก็จะคงเป็นตารางธรรมดาอยู่
เวลาจะเอาไปออก Report ก็ต้องเอาไปแสดงใน Table ธรรมดาไม่ใช่ CrossTab หรือ Matrix
เวลาจะเอาไป Bind ในฟอร์ม ก็เอาไปแสดงใน Grid ธรรมดา ไม่ได้ใช้คอนโทรลอะไรพิเศษ
และแน่นอนว่าเราไม่สามารถ Interact กับมันได้อย่าง Pivot Table จริงๆ (เอาเมาส์ไปจับมันหมุนไม่ได้ ว่างั้น)

คำัสั่งเพื่อการนี้ของ SQL Server ก็คือคำสั่ง pivot และ unpivot
pivot ทำหน้าที่ในการหมุนข้อมูลในแนว row ออกไปเป็นคอลัมน์แนวขวาง
unpivot ทำหน้าที่ในการหมุนคอลัมน์แนวขวาง ลงมาเป็นข้อมูลในแนว row
หลักการ หรือ syntax ของคำสั่ง pivot และ unpivot เป็นดังนี้

select *
from (select ROWGROUP_FIELDS, MEASURE_FIELD, DIM_FIELD
    from SOURCETABLE
) as src
pivot (
    AGG(MEARSURE_FIELD) for DIM_FIELD in (DIM_VALUE1, DIM_VALUE2, ...)
) as pv

โดยที่
ROWGROUP_FIELDS = รายการฟิลด์ที่จะใช้ในการ group ข้อมูลในแนว row (แนวตั้ง) ในตัวอย่างของเราคือฟิลด์ Month
MEASURE_FIELD = ชื่อฟิลด์ที่จะถูกรวมค่า ในตัวอย่างของเราก็คือฟิลด์ LineTotal
DIM_FIELD = ชื่อฟิลด์ที่จะถูกหมุนแกนจากแนวตั้งมาเป็นแนวนอน นั่นคือ Country
SOURCETABLE = ข้อมูลตั้งต้นของเรา จะมาจากการ Select ตารางหรือวิวหรืออะไรก็แล้วแต่
AGG = Aggregate function ที่จะใช้ในการรวมค่า ในตัวอย่างข้างต้นเราใช้ function Sum
DIM_VALUE = คือรายการของ “data” ที่เราต้องการนำมาแผ่ออกในแนวนอน ตรงนี้เองที่บอกว่าเราไม่สามารถทำแบบ dynamic ได้ เช่นในตัวอย่าง เราต้องกำหนดเอาไว้ล่วงหน้าว่าจะเอาประเทศอะไรบ้าง

ดังนั้นเพื่อที่จะ query ข้อมูลให้ได้ในลักษณะเดียวกับ Pivot Table ข้างต้น เราสามารถเขียนได้ดังนี้

select *
from(select [Month], LineTotal, Country
 from vw_Sales2003
)as src
pivot(
 Sum(LineTotal) for Country in (AU, CA, DE, FR, GB, US)
) as pv

และภาพต่อไปนี้คือผลจากการ select จริงใน SQL Server

คำสั่ง Unpivot ใน SQL Server
สำหรับคำสั่ง unpivot นั้นต้องขอยกอีกตัวอย่างหนึ่ง เพราะว่าสถานการณ์ในการใช้งานจะต่างกัน
unpivot มีไว้สำหรับหมุนข้อมูลในแนวขวาง หรือเปลี่ยนจากคอลัมน์ลงมาเป็นข้อมูลในแนว row
syntax ของคำสั่ง unpivot เป็นดังนี้

select *
from (select ROWGROUP_FIELDS, DIM_VALUE1, DIM_VALUE2, ...
    from SOURCETABLE
) as src
unpivot (
    MEASURE_FIELD for DIM_FIELD in (DIM_VALUE1, DIM_VALUE2, ...)
) as upv

โดยที่
ROWGROUP_FIELDS = รายการฟิลด์ที่เป็นตัว group ซึ่งจะถูก duplicate ในแนวตั้ง เมื่อทำ unpivot
DIM_VALUE = รายชื่อของฟิลด์ ที่จะถูกนำไปทำเป็น “ข้อมูล” ในแต่ละแถวที่จะถูกแตกออกมา
SOURCETABLE = ข้อมูลตั้งต้นของเรา จะมาจากการ Select ตารางหรือวิวหรืออะไรก็แล้วแต่
MEASURE_FIELD = ให้ตั้งชื่อฟิลด์ที่จะเกิดใหม่ เป็นฟิลด์ที่จะแสดงค่าจากข้อมูลที่จะถูกหมุน
DIM_FIELD = ให้ตั้งชื่อฟิลด์ที่จะเกิดใหม่ เป็นฟิลด์ที่จะแสดงให้เห็นว่าแต่ละ row นั้นมาจากคอลัมน์ไหน

ตัวอย่าง สมมติในตาราง SalesOrder เราเขียน query แบบนี้ (เอาแค่สอง record พอแล้วกันนะ จะได้เห็นภาพ)

select SalesOrderID, OrderDate, DueDate, ShipDate
from Sales.SalesOrderHeader
where SalesOrderID in (50000, 50001)

ข้อมูลตั้งต้นของเรา หน้าตาประมาณนี้

และถ้าเราเขียน query แบบนี้

select *
from(select SalesOrderID, OrderDate, DueDate, ShipDate
 from Sales.SalesOrderHeader
 where SalesOrderID in (50000, 50001)
) as src
unpivot (
 EventDate for EventType in (OrderDate, DueDate, ShipDate)
) as upv

ผลลัพธ์จะเป็น

ข้อจำกัดของคำสั่ง Pivot และ Unpivot
จะว่าไปแล้วคำสั่ง Pivot และ Unpivot ไม่ได้มีการทำงานอะไรเป็นพิเศษใน SQL Server
เพียงแต่เป็นคำสั่งที่ช่วยทำให้เขียน code ได้ง่าย สะอาดตา และสนุกขึ้น
ถ้าจะใช้สำนวนฝรั่งเค้าคงจะเรียกว่าเป็น syntactic sugar เท่านั้นเอง
อันที่จริงในสมัยก่อนที่จะมีคำสั่ง Pivot และ Unpivot เราก็ยังสามารถแก้ปัญหาลักษณะนี้ได้
โดยใช้เทคนิคการ case when then กับการ union นั่นเอง
และนั่นก็คือสิ่งที่คำสั่ง Pivot และ Unpivot ทำ
ก็คือทำให้เราเขียน sql ได้ง่ายๆ โดยที่ความจริงแล้ว
เกิดการ case when then หรือ union ที่ซับซ้อนขึ้นเป็นเบื้องหลัง

ที่ผมต้องขอกล่าวถึงวิธีการแบบเก่าอยู่ ก็เพราะว่าคำสั่ง Pivot หรือ Unpivot
แม้จะเขียนได้สั้น อ่านได้ง่าย แก้ไขง่าย แต่ข้อจำกัดของมันก็คือ
มันจัดการได้ครั้งละหนึ่ง Measure เท่านั้น และการ Pivot หรือ Unpivot
ก็สามารถทำได้แค่ Column Level หรือ Row Level ชั้นเดียว
หากเราไปเจอโจทย์ ที่ต้อง pivot ให้ได้ Column Level สองชั้น
เห็นจะต้อง back to basic คือกลับมาเขียน pivot ในแบบดั้งเดิมครับ

ก่อนอื่นผมจะเทียบให้เห็นว่า คำสั่ง pivot ที่เรากล่าวถึงข้างบนนั้น
ถ้าเราจะเขียนโดยใช้เทคนิค case when then ธรรมดานั้น จะเขียนได้อย่างไร
sql ที่ใช้คำสั่ง pivot

select *
from(select [Month], LineTotal, Country
 from vw_Sales2003
)as src
pivot(
 Sum(LineTotal) for Country in (AU, CA, DE, FR, GB, US)
) as pv

sql ที่ใช้การ case when then

select [Month],
Sum(case Country when 'AU' then LineTotal else 0 end) [AU],
Sum(case Country when 'CA' then LineTotal else 0 end) [CA],
Sum(case Country when 'DE' then LineTotal else 0 end) [DE],
Sum(case Country when 'FR' then LineTotal else 0 end) [FR],
Sum(case Country when 'GB' then LineTotal else 0 end) [GB],
Sum(case Country when 'US' then LineTotal else 0 end) [US]
from vw_Sales2003
group by [Month]

เห็นแล้วพอจะเข้าใจหลักการของคำสั่ง Pivot แล้วหรือยังครับ

คราวนี้ถ้าโจทย์ของเราคือ ให้แสดงข้อมูลโดยแบ่ง Column Level สองชั้น
ชั้นแรกเป็น Quarter ชั้นที่สองเป็น Country แต่ไม่ต้องแบ่ง Row Level
คราวนี้ล่ะครับถึงคราวอับจนสำหรับคำสั่ง Pivot
แต่ถ้าเราจะลุยเขียน sql ด้วยตัวเอง ก็สามารถเขียนได้แบบนี้

select
Sum(case when Quarter=1 and Country='AU' then LineTotal else 0 end) [Q1_AU],
Sum(case when Quarter=1 and Country='CA' then LineTotal else 0 end) [Q1_CA],
Sum(case when Quarter=1 and Country='DE' then LineTotal else 0 end) [Q1_DE],
Sum(case when Quarter=2 and Country='AU' then LineTotal else 0 end) [Q2_AU],
Sum(case when Quarter=2 and Country='CA' then LineTotal else 0 end) [Q2_CA],
Sum(case when Quarter=2 and Country='DE' then LineTotal else 0 end) [Q2_DE]
from vw_Sales2003

ผมเขียนแค่ 2 Quater กับ 3 ประเทศพอนะครับ แค่นี้ก็น่าจะทำให้พอเข้าใจประเด็น
งานต่อจากนี้ ก็อยู่ที่การแสดงผลล่ะครับ เพราะความจริงแล้ว ผลลัพธ์ มันก็คือ column ธรรมดาๆ นี่เอง
แต่เราอาจจะต้องไปวาดหัวตารางเป็น header สองชั้น ทำการ span สำหรับ cell ของ quarter อะไรอีกทีก็ว่าไป

ส่วนสำหรับคำสั่ง Unpivot แบบนี้นะครับ

select *
from(select SalesOrderID, OrderDate, DueDate, ShipDate
 from Sales.SalesOrderHeader
) as src
unpivot (
 EventDate for EventType in (OrderDate, DueDate, ShipDate)
) as upv

สามารถเขียนทดแทนด้วยการ union ธรรมดาดังนี้

select SalesOrderID, OrderDate EventDate, 'OrderDate' EventType
from Sales.SalesOrderHeader
union
select SalesOrderID, DueDate EventDate, 'DueDate' EventType
from Sales.SalesOrderHeader
union
select SalesOrderID, ShipDate EventDate, 'ShipDate' EventType
from Sales.SalesOrderHeader