กลับแถวตาราง (pivot/crosstab query)

เวลาทำรายงานให้แสดงรายรับ รายจ่าย รายปี โดยแสดงข้อมูลของแต่ละเดือนให้อยู่ในแถวเดียวกัน แบบนี้

cyear	month1	month2	month3	month4	month5	month6	month7	month8	month9	month10	month11	month12
2554	0	5	3	0	34	0	32	32	0	0	0	0
2555	29	765	97	99	0	0	98	9	98	0	0	22

แต่ในตารางเก็บแยกข้อมูลเป็นเดือนละแถว

cyear	cmonth	expense	income
2554	2	5	77
2554	3	3	15
2554	5	34	5
2554	7	32	64
2554	8	32	64
2554	9	0	0
2555	1	29	38
2555	2	765	32
2555	3	97	24
2555	4	99	8
2555	7	98	76
2555	8	9	89
2555	9	98	8
2555	11	0	98
2555	12	22	0

ถ้าคิวรี่ตามปกติ แถวหนึ่งก็ต้องคิวรี่ครั้งหนึ่ง แสดง 1 ปีก็คิวรี่ 12 ครั้ง ถ้าต้องการคิวรี่แค่ครั้งเดียวสามารถใช้วิธี crosstab ช่วยได้ ก่อนอื่นสร้างข้อมูลตัวอย่างก่อน

CREATE TABLE IF NOT EXISTS `account` (
  `cyear` int(4) NOT NULL,
  `cmonth` int(11) NOT NULL,
  `expense` decimal(10,0) NOT NULL,
  `income` decimal(10,0) NOT NULL,
  PRIMARY KEY (`cyear`,`cmonth`)
) ENGINE=InnoDB;

INSERT INTO `account` (`cyear`, `cmonth`, `expense`, `income`) VALUES
(2554, 2, 5, 77),
(2554, 3, 3, 15),
(2554, 5, 34, 5),
(2554, 7, 32, 64),
(2554, 8, 32, 64),
(2554, 9, 0, 0),
(2555, 1, 29, 38),
(2555, 2, 765, 32),
(2555, 3, 97, 24),
(2555, 4, 99, 8),
(2555, 7, 98, 76),
(2555, 8, 9, 89),
(2555, 9, 98, 8),
(2555, 11, 0, 98),
(2555, 12, 22, 0);

จากนั้นก็คิวรี่โดยใช้

SELECT `cyear`
,SUM(IF(`cmonth` = 1 ,expense ,0)) AS month1
,SUM(IF(`cmonth` = 2 ,expense ,0)) AS month2
,SUM(IF(`cmonth` = 3 ,expense ,0)) AS month3
,SUM(IF(`cmonth` = 4 ,expense ,0)) AS month4
,SUM(IF(`cmonth` = 5 ,expense ,0)) AS month5
,SUM(IF(`cmonth` = 6 ,expense ,0)) AS month6
,SUM(IF(`cmonth` = 7 ,expense ,0)) AS month7
,SUM(IF(`cmonth` = 8 ,expense ,0)) AS month8
,SUM(IF(`cmonth` = 9 ,expense ,0)) AS month9
,SUM(IF(`cmonth` = 10 ,expense ,0)) AS month10
,SUM(IF(`cmonth` = 11 ,expense ,0)) AS month11
,SUM(IF(`cmonth` = 12 ,expense ,0)) AS month12
FROM `account`
GROUP BY `cyear`;

จุดสังเกตคือ

1. ใช้ GROUP BY เพื่อรวมข้อมูลแต่ละที่สนใจให้อยู่แถวเดียวกัน ในที่นี้คือ ปี
2. การแสดงผลหลัง select จะใช้รูปแบบ ,SUM(IF(ฟิลย์ที่เป็นเงื่อนไข (เดือน) = ค่าที่สนใจ (ลำดับของเดือน) , ฟิลย์ที่ต้องการแสดง (expense) ,ค่าเริ่มต้นถ้าไม่มีข้อมูล)) AS ชื่อใหม่