PIVOT ใน SQL Server คือเครื่องมือที่ช่วยสร้างตารางสรุปในรูปแบบการเปลี่ยนการแสดงผลจากแนวตั้ง (Row) มาเป็นแนวนอน (Column) ซึ่งช่วยให้การดูข้อมูลสะดวกมากยิ่งขึ้น แต่เนื่องจาก การทำ PIVOT ใน SQL Server จำเป็นต้องทราบข้อมูลที่จะนำมาใช้เป็นชื่อคอลัมน์ในแนวนอนที่แน่นอน ซึ่งเหมาะกับการทำสรุป ยอดขายประจำเดือน ซึ่งมีแค่ เดือน 1 ถึงเดือน 12 หรือการสรุปตามวัน ซึ่งมี 1 - 31 ไม่เกินนี้ แต่ถ้าหากข้อมูลเรามีการเพิ่มลดอย่างต่อเนื่อง (แต่ต้องไม่มากจนเกินไป) จำเป็นต้องใช้วิธีการเขียนคำสั่งเพื่อทำให้ PIVOT สามารถแสดงผลเป็นแบบ Dynamic ได้ตามต้องการ
สำหรับตัวอย่างที่จะนำเสนอเป็นการสรุปยอด ตาม Category ของสินค้า ซึ่งจะแยกเป็นยอดขายแต่ละวัน โดยใช้ตาราง Category เป็นตารางที่จะนำมาเป็นตัวที่ทำ PIVOT ในส่วนของคอลัมน์ ซึ่งจะผันแปรไปตามข้อมูลที่มีอยู่ในตาราง Category ตาราง Product เก็บรายการสินค้า และ ตาราง Inv_dtl เก็บข้อมูลการขายสินค้า ตาราง inv_hd เก็บวันที่ขาย รายละเอียด ดังภาพ
ตัวอย่างตารางและข้อมูล
CREATE TABLE [category](
[cat_id] [nchar](10) NULL,
[cat_name] [nvarchar](50) NULL
)
CREATE TABLE [inv_dtl](
[inv_no] [nchar](10) NULL,
[product_id] [nchar](10) NULL,
[qty] [money] NULL,
[price] [money] NULL,
[amount] [money] NULL
)
CREATE TABLE [inv_hd](
[inv_no] [nchar](10) NULL,
[inv_date] [date] NULL
)
CREATE TABLE [product](
[product_id] [int] NULL,
[product_name] [varchar](50) NOT NULL,
[cat_id] [varchar](25) NULL
)
GO
INSERT [category] ([cat_id], [cat_name]) VALUES (N'C01', N'Beverage')
INSERT [category] ([cat_id], [cat_name]) VALUES (N'C02', N'Beauty')
INSERT [category] ([cat_id], [cat_name]) VALUES (N'C03', N'Health')
INSERT [category] ([cat_id], [cat_name]) VALUES (N'C04', N'Food')
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65001', N'101', 5.0000, 200.0000, 100.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65002', N'105', 1.0000, 80.0000, 80.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65002', N'104 ', 2.0000, 70.0000, 140.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65003', N'102', 3.0000, 50.0000, 150.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65003', N'104', 2.0000, 70.0000, 140.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65004', N'102', 2.0000, 80.0000, 160.0000)
INSERT [inv_dtl] ([inv_no], [product_id], [qty], [price], [amount]) VALUES (N'65005', N'101', 2.0000, 200.0000, 400.0000)
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65001', CAST(N'2022-05-22' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65002', CAST(N'2022-05-22' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65003', CAST(N'2022-05-23' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65004', CAST(N'2022-05-24' AS Date))
INSERT [inv_hd] ([inv_no], [inv_date]) VALUES (N'65005', CAST(N'2022-05-24' AS Date))
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (1, 101, N'Soda', N'C01')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (2, 102, N'Water', N'C01')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (3, 103, N'Pork', N'C04')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (4, 104, N'Vegetable', N'C04')
INSERT [product] ([product_id], [product_name], [cat_id]) VALUES (5, 105, N'Fruit', N'C04')
จากตารางข้างต้นถ้าเราจะทำรายงานสรุป ยอดขายตาม Category โดยแยกเป็นแต่ละวัน สามารถทำได้โดยใช้คำสั่ง
SELECT h.inv_date,c.cat_id,SUM(i.amount) as Total FROM inv_dtl i
LEFT JOIN inv_hd h ON i.inv_no = h.inv_no
LEFT JOIN product p ON i.product_id = p.product_id
LEFT JOIN category c ON p.cat_id = c.cat_id
GROUP BY c.cat_id,h.inv_date
ORDER BY h.inv_date,c.cat_id
จะได้ผลลัพธ์ดังภาพ
จะเห็นว่าข้อมูลข้างต้นจะเป็นข้อมูลสรุปยอดขายตาม Category โดยแยกสรุปเป็นวัน แต่เป็นรายการในลักษณะแนวตั้ง การดูรายงานจะทำให้เข้าใจได้ยาก หากต้องการนำเสนอในรูปแบบสรุป จำเป็นต้องอาศัยการใช้งานคำสั่ง PIVOT เพื่อกลับตารางจากแนวตั้งเป็นแนวนอน
แต่สิ่งที่เป็นโจทย์ในบทความนี้คอ ต้องนำรายการของ Category ทั้งหมด มาแสดงเป็นหัวคอลัมน์ ดังนั้นต้องนำข้อมูลในตาราง Category ทั้งหมดมาแปลงก่อนนำไปใช้ในคำสั่ง PIVOT
ซึ่งลักษณะนี้จะเป็นรูปแบบ Dynamic ที่ข้อมูลไม่ได้คงที่อาจมีการเพิ่มหรือลดลงได้ ดังนั้นจะต้องทำการ เตรียมข้อมูลสำหรับ PIVOT โดยให้ข้อมูลในตารางที่เป็น cat_id ทุกรายการ เอามาทำให้ให้อยู่ในรูปแบบดังนี้
C01,C02,C03,C04
สามารถทำได้โดยการเขียนคำสั่งดังนี้
DECLARE @colList varchar(MAX)
SELECT @colList = COALESCE(@colList + ',','') + RTRIM(cat_id)
FROM category
SELECT @colList
จะได้ผลลัพธ์ดังภาพ
จากนั้นนำผลลัพธ์ที่เก็บในตัวแปร @colList ไปใช้ในคำสั่ง PIVOT โดยเขียนคำสั่งในรูปของตัวแปร และเรียกประมวลผลผ่านคำสั่ง EXEC ดังนี้
DECLARE @colList varchar(MAX)
SELECT @colList = COALESCE(@colList + ',','') + RTRIM(cat_id) FROM category
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery = 'SELECT * FROM
(
SELECT h.inv_date,c.cat_id,SUM(i.amount) as Total FROM inv_dtl i
LEFT JOIN inv_hd h ON i.inv_no = h.inv_no
LEFT JOIN product p ON i.product_id = p.product_id
LEFT JOIN category c ON p.cat_id = c.cat_id
GROUP BY c.cat_id,h.inv_date
) t
PIVOT(
SUM(total)
FOR cat_id IN ('+ @colList +')
) AS pivot_table;';
EXEC(@SQLQuery)
จะได้ผลลัพธ์ตามภาพ
หากไม่ต้องการ คอลัมน์ เป็นรหัส C01 C02 C03 C04 สามารถใช้ชื่อ cat_name มาแทนได้ แต่ต้องระวังเรื่องของความยาว โดยที่ชื่อนั้นไม่ควรยาวมากจนเกินไป และควรใส่ [ ] คลุมระหว่างชื่อด้วยเพื่อป้องกันเรื่องของการเว้นช่องว่างระหว่างคำ
DECLARE @colList varchar(MAX)
SELECT @colList = COALESCE(@colList + ',','') + '[' + RTRIM(cat_name)+ ']' FROM category
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery = 'SELECT * FROM
(
SELECT h.inv_date,c.cat_name,SUM(i.amount) as Total FROM inv_dtl i
LEFT JOIN inv_hd h ON i.inv_no = h.inv_no
LEFT JOIN product p ON i.product_id = p.product_id
LEFT JOIN category c ON p.cat_id = c.cat_id
GROUP BY c.cat_name,h.inv_date
) t
PIVOT(
SUM(total)
FOR cat_name IN ('+ @colList +')
) AS pivot_table;';
EXEC(@SQLQuery)
จะได้ผลลัพธ์ ดังภาพ
ขอให้สนุกกับการกลับตาราง
สวัสดี ร่ำรวย ดูหนังกลางแปลง ปิ้งปลาหมึก อร่อย
