Tag Archive pivot

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

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

ถ้าคิวรี่ตามปกติ แถวหนึ่งก็ต้องคิวรี่ครั้งหนึ่ง แสดง 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 ชื่อใหม่