{"id":499,"date":"2018-01-21T16:06:35","date_gmt":"2018-01-21T09:06:35","guid":{"rendered":"http:\/\/www.oopsbox.com\/?p=499"},"modified":"2018-02-02T23:03:54","modified_gmt":"2018-02-02T16:03:54","slug":"dynamic-pivoting-in-mysql","status":"publish","type":"post","link":"https:\/\/www.oopsbox.com\/?p=499","title":{"rendered":"Dynamic pivoting in MySQL"},"content":{"rendered":"<p>\u0e04\u0e34\u0e14\u0e16\u0e36\u0e07\u0e23\u0e32\u0e22\u0e07\u0e32\u0e19\u0e04\u0e34\u0e14\u0e16\u0e36\u0e07 Excel 555 &#8230; \u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e04\u0e27\u0e32\u0e21\u0e15\u0e31\u0e49\u0e07\u0e43\u0e08\u0e43\u0e19\u0e01\u0e32\u0e23\u0e40\u0e23\u0e35\u0e22\u0e19 Microsoft Excel \u0e19\u0e35\u0e48\u0e21\u0e35\u0e21\u0e32\u0e2a\u0e31\u0e01\u0e1e\u0e31\u0e01\u0e41\u0e25\u0e49\u0e27 \u0e40\u0e23\u0e34\u0e48\u0e21\u0e14\u0e49\u0e27\u0e22\u0e01\u0e32\u0e23\u0e0b\u0e37\u0e49\u0e2d\u0e2b\u0e19\u0e31\u0e07\u0e2a\u0e37\u0e2d\u0e21\u0e32\u0e01\u0e48\u0e2d\u0e19\u0e41\u0e15\u0e48\u0e19\u0e48\u0e32\u0e08\u0e30\u0e2b\u0e25\u0e32\u0e22\u0e40\u0e14\u0e37\u0e2d\u0e19\u0e25\u0e30 (\u0e08\u0e19\u0e25\u0e37\u0e21\u0e44\u0e1b\u0e40\u0e25\u0e22\u0e27\u0e48\u0e32\u0e0b\u0e37\u0e49\u0e2d\u0e21\u0e32\u0e15\u0e2d\u0e19\u0e44\u0e2b\u0e19 \u0e1a\u0e32\u0e1b\u0e21\u0e32\u0e01) \u0e41\u0e15\u0e48\u0e01\u0e47\u0e19\u0e30\u0e04\u0e27\u0e32\u0e21\u0e02\u0e35\u0e49\u0e40\u0e01\u0e35\u0e22\u0e08\u0e04\u0e23\u0e2d\u0e1a\u0e07\u0e33 \u0e0a\u0e48\u0e27\u0e07\u0e19\u0e35\u0e49\u0e01\u0e47\u0e40\u0e25\u0e22\u0e40\u0e1b\u0e47\u0e19\u0e0a\u0e48\u0e27\u0e07\u0e2d\u0e48\u0e32\u0e19\u0e2b\u0e19\u0e31\u0e07\u0e2a\u0e37\u0e2d Microsoft Excel \u0e41\u0e1a\u0e1a\u0e08\u0e23\u0e34\u0e07\u0e08\u0e31\u0e07 (\u0e41\u0e2d\u0e1a\u0e15\u0e31\u0e49\u0e07\u0e43\u0e08)<br \/>\n\u0e40\u0e1b\u0e34\u0e14\u0e14\u0e39\u0e04\u0e23\u0e48\u0e32\u0e27 \u0e46 \u0e21\u0e35\u0e1a\u0e17\u0e19\u0e36\u0e07\u0e1e\u0e39\u0e14\u0e16\u0e36\u0e07\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e17\u0e33 Pivot Table \u0e43\u0e19\u0e42\u0e1b\u0e23\u0e41\u0e01\u0e23\u0e21\u0e15\u0e23\u0e30\u0e01\u0e39\u0e25\u0e15\u0e32\u0e23\u0e32\u0e07\u0e04\u0e33\u0e19\u0e27\u0e13\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e19\u0e35\u0e49\u0e17\u0e38\u0e01\u0e15\u0e31\u0e27\u0e43\u0e19\u0e15\u0e25\u0e32\u0e14\u0e01\u0e47\u0e2a\u0e32\u0e21\u0e32\u0e23\u0e16\u0e17\u0e33\u0e44\u0e14\u0e49 \u0e16\u0e37\u0e2d\u0e40\u0e1b\u0e47\u0e19\u0e2b\u0e19\u0e36\u0e48\u0e07\u0e43\u0e19 Killer Feature \u0e41\u0e15\u0e48\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e19\u0e35\u0e49\u0e41\u0e04\u0e48\u0e1b\u0e23\u0e30\u0e40\u0e14\u0e47\u0e19\u0e19\u0e34\u0e14\u0e2b\u0e19\u0e48\u0e2d\u0e22 ^_^<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-504\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/PivotingMySQL.png\" alt=\"\" width=\"800\" height=\"250\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/PivotingMySQL.png 800w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/PivotingMySQL-300x94.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/PivotingMySQL-768x240.png 768w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/PivotingMySQL-700x219.png 700w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/p>\n<p>\u0e2a\u0e33\u0e2b\u0e23\u0e31\u0e1a\u0e04\u0e19\u0e17\u0e35\u0e48\u0e40\u0e02\u0e35\u0e22\u0e19\u0e04\u0e33\u0e2a\u0e31\u0e48\u0e07\u0e40\u0e23\u0e35\u0e22\u0e01\u0e14\u0e39\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 (SQL) \u0e01\u0e47\u0e17\u0e33\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e19\u0e35\u0e49\u0e2d\u0e22\u0e39\u0e48\u0e40\u0e19\u0e37\u0e2d\u0e07 \u0e46 \u0e2b\u0e25\u0e32\u0e22\u0e04\u0e23\u0e31\u0e49\u0e07\u0e01\u0e47\u0e17\u0e33\u0e41\u0e1a\u0e1a\u0e2e\u0e32\u0e23\u0e4c\u0e14\u0e04\u0e2d\u0e23\u0e4c \u0e13 \u0e15\u0e2d\u0e19\u0e19\u0e31\u0e49\u0e19\u0e40\u0e25\u0e22 \u0e15\u0e31\u0e27\u0e2d\u0e22\u0e48\u0e32\u0e07\u0e04\u0e23\u0e48\u0e32\u0e27 \u0e46 \u0e2a\u0e21\u0e21\u0e38\u0e15\u0e34\u0e40\u0e23\u0e32\u0e21\u0e35\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e04\u0e48\u0e32\u0e43\u0e0a\u0e49\u0e08\u0e48\u0e32\u0e22\u0e20\u0e32\u0e22\u0e43\u0e19\u0e1a\u0e49\u0e32\u0e19\u0e2a\u0e23\u0e38\u0e1b\u0e1b\u0e23\u0e30\u0e21\u0e32\u0e13\u0e19\u0e35\u0e49 \u0e43\u0e19\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 (\u0e14\u0e49\u0e32\u0e19\u0e0b\u0e49\u0e32\u0e22) \u0e41\u0e25\u0e30\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e41\u0e2a\u0e14\u0e07\u0e1c\u0e25\u0e40\u0e1b\u0e47\u0e19\u0e2d\u0e35\u0e01\u0e23\u0e39\u0e1b\u0e14\u0e49\u0e32\u0e19\u0e02\u0e27\u0e32<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-500\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121141337-727x261.png\" alt=\"\" width=\"727\" height=\"261\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121141337-727x261.png 727w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121141337-727x261-300x108.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121141337-727x261-700x251.png 700w\" sizes=\"auto, (max-width: 727px) 100vw, 727px\" \/><\/p>\n<p>SQL \u0e41\u0e2a\u0e14\u0e07\u0e23\u0e32\u0e22\u0e07\u0e32\u0e19\u0e41\u0e1a\u0e1a\u0e19\u0e35\u0e49 \u0e2d\u0e19\u0e38\u0e21\u0e32\u0e19\u0e08\u0e32\u0e01\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e14\u0e34\u0e1a\u0e17\u0e35\u0e48\u0e40\u0e01\u0e47\u0e1a\u0e43\u0e19\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 \u0e01\u0e48\u0e2d\u0e19\u0e17\u0e35\u0e48\u0e40\u0e23\u0e32\u0e08\u0e30\u0e2a\u0e23\u0e38\u0e1b\u0e44\u0e14\u0e49\u0e01\u0e47\u0e19\u0e48\u0e32\u0e08\u0e30\u0e21\u0e35\u0e1b\u0e23\u0e30\u0e21\u0e32\u0e13\u0e19\u0e35\u0e49 (\u0e40\u0e23\u0e32\u0e43\u0e0a\u0e49\u0e1a\u0e23\u0e34\u0e01\u0e32\u0e23\u0e02\u0e2d\u0e07 <a href=\"http:\/\/www.generatedata.com\">generatedata.com<\/a>) \u0e40\u0e2d\u0e32\u0e15\u0e31\u0e27\u0e2d\u0e22\u0e48\u0e32\u0e07\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e2a\u0e31\u0e01 100 \u0e23\u0e32\u0e22\u0e01\u0e32\u0e23\u0e2b\u0e25\u0e48\u0e30\u0e01\u0e31\u0e19<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">DROP TABLE IF EXISTS `ExpenseCategory`;\r\nCREATE TABLE `ExpenseCategory` (\r\n  `CategoryId` varchar(255) NOT NULL,\r\n  `CategoryName` varchar(255) default NULL,\r\n  PRIMARY KEY (`CategoryId`)\r\n);\r\n\r\nINSERT INTO `ExpenseCategory` (`CategoryId`,`CategoryName`) VALUES (\"01\",\"\u0e04\u0e27\u0e32\u0e21\u0e1a\u0e31\u0e19\u0e40\u0e17\u0e34\u0e07\"),(\"02\",\"\u0e02\u0e2d\u0e07\u0e0a\u0e33\"),(\"03\",\"\u0e04\u0e23\u0e2d\u0e1a\u0e04\u0e23\u0e31\u0e27\"),(\"04\",\"\u0e01\u0e32\u0e23\u0e04\u0e21\u0e19\u0e32\u0e04\u0e21\u0e02\u0e19\u0e2a\u0e48\u0e07\");\r\n\r\nDROP TABLE IF EXISTS `Expense`;\r\nCREATE TABLE `Expense` (\r\n  `id` mediumint(8) unsigned NOT NULL auto_increment,\r\n  `CategoryId` varchar(255) default NULL,\r\n  `ExpenseDate` varchar(255),\r\n  `Amount` varchar(100) default NULL,\r\n  PRIMARY KEY (`id`)\r\n) AUTO_INCREMENT=1;\r\n\r\nINSERT INTO `Expense` VALUES (1,'04','2018-07-19 02:55:09','83369'),(2,'01','2018-01-17 19:18:21','28001'),(3,'04','2018-06-29 03:32:27','17058'),(4,'04','2018-07-04 17:44:03','64619'),(5,'04','2018-07-22 17:38:56','24050'),(6,'04','2018-08-15 03:43:50','80585'),(7,'03','2018-06-11 23:52:59','79427'),(8,'02','2018-04-29 04:03:38','63177'),(9,'01','2018-09-03 04:02:46','77975'),(10,'04','2018-07-04 21:49:15','61752'),(11,'01','2018-02-21 11:39:34','59245'),(12,'04','2018-01-05 05:42:14','15695'),(13,'02','2018-09-13 09:07:51','22132'),(14,'02','2018-08-12 13:24:09','58156'),(15,'01','2018-08-14 18:34:16','7802'),(16,'01','2018-04-04 04:26:02','44733'),(17,'03','2018-11-03 02:59:01','73158'),(18,'04','2018-01-23 09:59:45','69724'),(19,'02','2018-11-16 07:39:50','98963'),(20,'03','2018-01-04 11:20:59','93659'),(21,'02','2018-08-25 22:01:10','13945'),(22,'04','2018-04-08 22:46:07','90735'),(23,'01','2018-09-13 10:20:59','76856'),(24,'01','2018-09-02 08:45:17','54668'),(25,'03','2018-12-06 12:19:30','45571'),(26,'02','2018-05-27 09:48:40','24694'),(27,'01','2018-04-15 03:01:29','73961'),(28,'01','2018-03-11 06:05:34','53546'),(29,'02','2018-12-27 06:57:17','91375'),(30,'04','2018-02-05 05:14:19','44997'),(31,'02','2018-12-15 02:35:52','54144'),(32,'02','2018-08-01 02:06:37','95898'),(33,'02','2018-02-18 08:50:43','79249'),(34,'02','2018-03-26 04:39:35','66184'),(35,'01','2018-07-26 13:17:03','44670'),(36,'02','2018-12-26 13:50:39','88500'),(37,'04','2018-04-19 20:26:25','31414'),(38,'01','2018-07-28 13:53:24','56802'),(39,'02','2018-04-27 06:18:15','67682'),(40,'01','2018-10-11 12:25:37','98233'),(41,'03','2018-09-28 12:36:05','43118'),(42,'02','2018-08-17 16:06:34','94942'),(43,'02','2018-12-16 16:28:16','25039'),(44,'04','2018-04-14 15:47:06','87368'),(45,'03','2018-11-21 16:53:01','49355'),(46,'04','2018-01-06 17:08:54','63354'),(47,'03','2018-02-02 11:06:33','26540'),(48,'01','2018-10-19 03:50:49','32534'),(49,'01','2018-05-15 23:40:11','58038'),(50,'03','2018-04-28 23:53:24','2915'),(51,'01','2018-03-30 18:57:12','10229'),(52,'03','2018-04-22 07:47:45','20004'),(53,'03','2018-08-16 23:42:08','69185'),(54,'02','2018-03-09 20:38:52','92533'),(55,'02','2018-12-25 23:51:10','66589'),(56,'04','2018-09-22 05:40:21','94759'),(57,'01','2018-06-03 13:24:32','76588'),(58,'03','2018-04-21 07:27:01','66573'),(59,'02','2018-09-25 03:49:02','76607'),(60,'01','2018-08-11 10:48:24','47755'),(61,'04','2018-11-02 02:37:54','69570'),(62,'02','2018-08-31 14:44:15','25560'),(63,'02','2018-03-23 07:24:59','78550'),(64,'04','2018-06-07 17:39:12','18455'),(65,'02','2018-10-31 11:14:30','71227'),(66,'01','2018-10-31 14:23:16','2454'),(67,'04','2018-08-14 21:16:17','54700'),(68,'04','2018-06-05 01:04:59','59554'),(69,'01','2018-01-23 10:33:50','37798'),(70,'04','2018-06-09 12:00:56','59983'),(71,'04','2018-02-19 15:56:33','56754'),(72,'04','2018-12-24 13:14:33','54684'),(73,'02','2018-05-15 23:08:16','66531'),(74,'02','2018-06-25 19:08:32','25407'),(75,'02','2018-11-25 18:53:14','96119'),(76,'01','2018-05-28 02:43:26','30991'),(77,'04','2018-03-18 02:03:29','81845'),(78,'02','2018-02-18 20:23:03','1057'),(79,'01','2018-11-15 16:32:26','52151'),(80,'01','2018-07-25 19:54:37','91069'),(81,'04','2018-04-18 14:02:13','51134'),(82,'02','2018-06-04 07:27:50','4609'),(83,'04','2018-07-07 20:22:38','62669'),(84,'02','2018-08-13 10:40:36','93161'),(85,'03','2018-03-29 12:51:47','75717'),(86,'04','2018-04-03 15:35:12','67327'),(87,'04','2018-02-06 15:56:58','87785'),(88,'01','2018-12-16 04:38:52','34503'),(89,'03','2018-07-25 02:52:09','87144'),(90,'04','2018-08-21 21:48:58','37756'),(91,'01','2018-07-25 20:38:00','5070'),(92,'01','2018-05-23 17:56:31','40138'),(93,'01','2018-01-10 22:47:36','35626'),(94,'01','2018-03-20 22:01:19','94446'),(95,'01','2018-05-20 23:55:32','65411'),(96,'02','2018-08-06 07:52:03','48145'),(97,'02','2018-05-26 22:38:07','17564'),(98,'01','2018-05-31 21:40:52','59694'),(99,'03','2018-03-03 16:47:55','18672'),(100,'01','2018-04-29 06:15:51','52622');\r\n<\/pre>\n<p>\u0e1b\u0e01\u0e15\u0e34(\u0e40\u0e23\u0e32)\u0e40\u0e02\u0e35\u0e22\u0e19 SQL \u0e40\u0e1e\u0e37\u0e48\u0e2d\u0e2a\u0e23\u0e38\u0e1b\u0e04\u0e48\u0e32\u0e43\u0e0a\u0e49\u0e08\u0e48\u0e32\u0e22\u0e41\u0e22\u0e01\u0e15\u0e32\u0e21\u0e1b\u0e23\u0e30\u0e40\u0e20\u0e17\u0e41\u0e25\u0e30\u0e23\u0e32\u0e22\u0e40\u0e14\u0e37\u0e2d\u0e19\u0e15\u0e32\u0e21\u0e15\u0e32\u0e23\u0e32\u0e07\u0e23\u0e39\u0e1b\u0e02\u0e27\u0e32<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT\r\n    CategoryName, \r\n    SUM(IF(M = 1, TotalAmount, 0 )) AS '1',\r\n    SUM(IF(M = 2, TotalAmount, 0 )) AS '2',\r\n    SUM(IF(M = 3, TotalAmount, 0 )) AS '3',\r\n    SUM(IF(M = 4, TotalAmount, 0 )) AS '4',\r\n    SUM(IF(M = 5, TotalAmount, 0 )) AS '5',\r\n    SUM(IF(M = 6, TotalAmount, 0 )) AS '6',\r\n    SUM(IF(M = 7, TotalAmount, 0 )) AS '7',\r\n    SUM(IF(M = 8, TotalAmount, 0 )) AS '8',\r\n    SUM(IF(M = 9, TotalAmount, 0 )) AS '9',\r\n    SUM(IF(M = 10, TotalAmount, 0 )) AS '10',\r\n    SUM(IF(M = 11, TotalAmount, 0 )) AS '11',\r\n    SUM(IF(M = 12, TotalAmount, 0 )) AS '12',\r\n    SUM(TotalAmount) AS Total\r\nFROM (\r\n    SELECT\r\n        MONTH(Expense.ExpenseDate) AS M,\r\n        MONTHNAME(Expense.ExpenseDate) AS MN,\r\n        Expense.CategoryId, \r\n        ExpenseCategory.CategoryName, \r\n        SUM(Amount) AS TotalAmount\r\n    FROM Expense INNER JOIN ExpenseCategory ON Expense.CategoryId = ExpenseCategory.CategoryId\r\n    GROUP BY Expense.CategoryId, MONTH(Expense.ExpenseDate)\r\n) T \r\nGROUP BY T.CategoryId \r\nORDER BY T.CategoryId ASC;<\/pre>\n<p>\u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e01\u0e47\u0e08\u0e30\u0e1b\u0e23\u0e30\u0e21\u0e32\u0e13\u0e19\u0e35\u0e49<\/p>\n<ul>\n<li>\u0e41\u0e16\u0e27\u0e40\u0e1b\u0e47\u0e19\u0e1b\u0e23\u0e30\u0e40\u0e20\u0e17\u0e04\u0e48\u0e32\u0e43\u0e0a\u0e49\u0e08\u0e48\u0e32\u0e22<\/li>\n<li>\u0e04\u0e2d\u0e25\u0e31\u0e21\u0e20\u0e4c\u0e40\u0e1b\u0e47\u0e19\u0e22\u0e2d\u0e14\u0e23\u0e27\u0e21\u0e23\u0e32\u0e22\u0e40\u0e14\u0e37\u0e2d\u0e19<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-501\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121154017-913x171.png\" alt=\"\" width=\"913\" height=\"171\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121154017-913x171.png 913w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121154017-913x171-300x56.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121154017-913x171-768x144.png 768w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121154017-913x171-700x131.png 700w\" sizes=\"auto, (max-width: 913px) 100vw, 913px\" \/><\/p>\n<p>\u0e41\u0e1a\u0e1a\u0e19\u0e35\u0e49\u0e1a\u0e48\u0e2d\u0e22 \u0e46 \u0e01\u0e47\u0e40\u0e2b\u0e19\u0e37\u0e48\u0e2d\u0e22\u0e40\u0e2b\u0e21\u0e37\u0e2d\u0e19\u0e01\u0e31\u0e19\u0e19\u0e30\u00a0 (\u0e2d\u0e32\u0e08\u0e21\u0e35\u0e04\u0e19\u0e40\u0e02\u0e35\u0e22\u0e19 SQL \u0e2a\u0e31\u0e49\u0e19\u0e41\u0e25\u0e30\u0e07\u0e48\u0e32\u0e22\u0e01\u0e27\u0e48\u0e32\u0e19\u0e35\u0e49\u0e41\u0e2b\u0e25\u0e48\u0e30\u00a0 \u0e01\u0e47\u0e0a\u0e48\u0e27\u0e22\u0e41\u0e19\u0e30\u0e19\u0e33\u0e14\u0e49\u0e27\u0e22\u0e2b\u0e25\u0e48\u0e30\u0e01\u0e31\u0e19\u0e04\u0e23\u0e31\u0e1a) \u0e21\u0e31\u0e19\u0e15\u0e49\u0e2d\u0e07\u0e21\u0e35\u0e04\u0e19\u0e40\u0e04\u0e22\u0e40\u0e2b\u0e19\u0e37\u0e48\u0e2d\u0e22\u0e40\u0e2b\u0e21\u0e37\u0e2d\u0e19\u0e40\u0e23\u0e32\u0e19\u0e30 \u0e41\u0e25\u0e30\u0e01\u0e47\u0e21\u0e35\u0e08\u0e23\u0e34\u0e07\u0e14\u0e49\u0e27\u0e22 \u0e04\u0e38\u0e13 Rick James \u0e40\u0e04\u0e49\u0e32\u0e01\u0e47\u0e17\u0e33 Function \u0e40\u0e1e\u0e37\u0e48\u0e2d\u0e17\u0e33 Pivot Table \u0e44\u0e27\u0e49\u0e40\u0e2b\u0e21\u0e37\u0e2d\u0e19\u0e01\u0e31\u0e19 (<a href=\"http:\/\/mysql.rjweb.org\/\">Blog \u0e19\u0e35\u0e49\u0e41\u0e19\u0e30\u0e19\u0e33\u0e43\u0e2b\u0e49\u0e2d\u0e48\u0e32\u0e19\u0e40\u0e25\u0e22<\/a>)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">DELIMITER \/\/\r\nDROP   PROCEDURE IF EXISTS PIVOTTABLE \/\/\r\nCREATE PROCEDURE PIVOTTABLE(\r\n    IN tbl_name VARCHAR(99),       -- table name (or db.tbl)\r\n    IN base_cols VARCHAR(99),      -- column(s) on the left, separated by commas\r\n    IN pivot_col VARCHAR(64),      -- name of column to put across the top\r\n    IN tally_col VARCHAR(64),      -- name of column to SUM up\r\n    IN where_clause VARCHAR(99),   -- empty string or \"WHERE ...\"\r\n    IN order_by VARCHAR(99)        -- empty string or \"ORDER BY ...\"; usually the base_cols\r\n    )\r\n    DETERMINISTIC\r\n    SQL SECURITY INVOKER\r\nBEGIN\r\n    -- Find the distinct values\r\n    -- Build the SUM()s\r\n    SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',\r\n                    ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');\r\n    -- select @subq;\r\n\r\n    SET @cc1 = \"CONCAT('SUM(IF(&amp;p = ', &amp;v, ', &amp;t, 0)) AS ', &amp;v)\";\r\n    SET @cc2 = REPLACE(@cc1, '&amp;p', pivot_col);\r\n    SET @cc3 = REPLACE(@cc2, '&amp;t', tally_col);\r\n    -- select @cc2, @cc3;\r\n    SET @qval = CONCAT(\"'\\\"', val, '\\\"'\");\r\n    -- select @qval;\r\n    SET @cc4 = REPLACE(@cc3, '&amp;v', @qval);\r\n    -- select @cc4;\r\n\r\n    SET SESSION group_concat_max_len = 10000;   -- just in case\r\n    SET @stmt = CONCAT(\r\n            'SELECT  GROUP_CONCAT(', @cc4, ' SEPARATOR \",\\n\")  INTO @sums',\r\n            ' FROM ( ', @subq, ' ) AS top');\r\n     select @stmt;\r\n    PREPARE _sql FROM @stmt;\r\n    EXECUTE _sql;                      -- Intermediate step: build SQL for columns\r\n    DEALLOCATE PREPARE _sql;\r\n    -- Construct the query and perform it\r\n    SET @stmt2 = CONCAT(\r\n            'SELECT ',\r\n                base_cols, ',\\n',\r\n                @sums,\r\n                ',\\n SUM(', tally_col, ') AS Total'\r\n            '\\n FROM ', tbl_name, ' ',\r\n            where_clause,\r\n            ' GROUP BY ', base_cols,\r\n            '\\n WITH ROLLUP',\r\n            '\\n', order_by\r\n        );\r\n    select @stmt2;                    -- The statement that generates the result\r\n    PREPARE _sql FROM @stmt2;\r\n    EXECUTE _sql;                     -- The resulting pivot table ouput\r\n    DEALLOCATE PREPARE _sql;\r\n    -- For debugging \/ tweaking, SELECT the various @variables after CALLing.\r\nEND;\r\n\/\/\r\nDELIMITER ;<\/pre>\n<p>\u0e27\u0e34\u0e18\u0e35\u0e40\u0e23\u0e35\u0e22\u0e01\u0e43\u0e0a\u0e49\u0e07\u0e32\u0e19\u0e01\u0e47\u0e07\u0e48\u0e32\u0e22 \u0e46 \u0e40\u0e25\u0e22\u0e41\u0e1a\u0e1a\u0e19\u0e35\u0e49<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CALL PIVOTTABLE('TEMP', 'CategoryName', 'M', 'TotalAmount', '', ' ORDER BY CategoryId');<\/pre>\n<p>\u0e15\u0e32\u0e23\u0e32\u0e07 TEMP \u0e01\u0e47\u0e2a\u0e23\u0e49\u0e32\u0e07\u0e02\u0e36\u0e49\u0e19\u0e21\u0e32\u0e08\u0e32\u0e01\u0e2a\u0e23\u0e38\u0e1b (\u0e15\u0e32\u0e23\u0e32\u0e07\u0e1d\u0e31\u0e48\u0e07\u0e0b\u0e49\u0e32\u0e22\u0e08\u0e32\u0e01\u0e23\u0e39\u0e1b\u0e41\u0e23\u0e01)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-502\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155107-573x236.png\" alt=\"\" width=\"573\" height=\"236\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155107-573x236.png 573w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155107-573x236-300x124.png 300w\" sizes=\"auto, (max-width: 573px) 100vw, 573px\" \/><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">DROP TABLE IF EXISTS TEMP;\r\nCREATE TABLE TEMP (\r\n    SELECT\r\n        MONTH(Expense.ExpenseDate) AS M,\r\n        MONTHNAME(Expense.ExpenseDate) AS MN,\r\n        Expense.CategoryId AS CategoryId, \r\n        ExpenseCategory.CategoryName AS CategoryName, \r\n        SUM(Amount) AS TotalAmount\r\n    FROM Expense INNER JOIN ExpenseCategory ON Expense.CategoryId = ExpenseCategory.CategoryId\r\n    GROUP BY Expense.CategoryId, MONTH(Expense.ExpenseDate)\r\n);\r\n\r\nSELECT * FROM TEMP;<\/pre>\n<p>\u0e2b\u0e25\u0e31\u0e07\u0e08\u0e32\u0e01\u0e40\u0e23\u0e35\u0e22\u0e01\u0e1f\u0e31\u0e07\u0e01\u0e4c\u0e0a\u0e31\u0e48\u0e19\u0e40\u0e2a\u0e23\u0e47\u0e08\u0e1c\u0e25\u0e25\u0e31\u0e1e\u0e18\u0e4c\u0e01\u0e47\u0e40\u0e2b\u0e21\u0e37\u0e2d\u0e19\u0e17\u0e35\u0e48\u0e40\u0e23\u0e32\u0e40\u0e02\u0e35\u0e22\u0e19\u0e40\u0e2d\u0e07\u0e19\u0e31\u0e48\u0e19\u0e41\u0e2b\u0e25\u0e48\u0e30 ^_^ \u0e41\u0e15\u0e48\u0e25\u0e14\u0e41\u0e23\u0e07\u0e07\u0e32\u0e19\u0e44\u0e14\u0e49\u0e2d\u0e35\u0e01<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CALL PIVOTTABLE('TEMP', 'CategoryName', 'M', 'TotalAmount', '', ' ORDER BY CategoryId');<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-503\" src=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155436-918x179.png\" alt=\"\" width=\"918\" height=\"179\" srcset=\"https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155436-918x179.png 918w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155436-918x179-300x58.png 300w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155436-918x179-768x150.png 768w, https:\/\/www.oopsbox.com\/wp-content\/uploads\/Screenshot-20180121155436-918x179-700x136.png 700w\" sizes=\"auto, (max-width: 918px) 100vw, 918px\" \/><\/p>\n<p>\u0e1b.\u0e25.<\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li><a href=\"https:\/\/support.office.com\/th-th\/article\/%E0%B8%AA%E0%B8%A3%E0%B9%89%E0%B8%B2%E0%B8%87-pivottable-%E0%B9%80%E0%B8%9E%E0%B8%B7%E0%B9%88%E0%B8%AD%E0%B8%A7%E0%B8%B4%E0%B9%80%E0%B8%84%E0%B8%A3%E0%B8%B2%E0%B8%B0%E0%B8%AB%E0%B9%8C%E0%B8%82%E0%B9%89%E0%B8%AD%E0%B8%A1%E0%B8%B9%E0%B8%A5%E0%B9%83%E0%B8%99%E0%B9%80%E0%B8%A7%E0%B8%B4%E0%B8%A3%E0%B9%8C%E0%B8%81%E0%B8%8A%E0%B8%B5%E0%B8%95-a9a84538-bfe9-40a9-a8e9-f99134456576\">\u0e2a\u0e23\u0e49\u0e32\u0e07 PivotTable \u0e40\u0e1e\u0e37\u0e48\u0e2d\u0e27\u0e34\u0e40\u0e04\u0e23\u0e32\u0e30\u0e2b\u0e4c\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e43\u0e19\u0e40\u0e27\u0e34\u0e23\u0e4c\u0e01\u0e0a\u0e35\u0e15<\/a><\/li>\n<li><a href=\"http:\/\/mysql.rjweb.org\/\">MySQL Documents by Rick James<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<div id=\"s-share-buttons\" class=\"\"><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"fb-share-button\" data-href=\"https:\/\/www.oopsbox.com\/?p=499\" data-type=\"button_count\"><\/div><\/div><div class=\"s-single-share\"><a href=\"https:\/\/twitter.com\/share\" class=\"twitter-share-button\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"g-plusone\" data-size=\"medium\"><\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"\/\/www.pinterest.com\/pin\/create\/button\/\" data-pin-do=\"buttonBookmark\"  data-pin-color=\"red\"><img decoding=\"async\" src=\"\/\/assets.pinterest.com\/images\/pidgets\/pinit_fg_en_rect_red_20.png\" \/><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<script type=\"text\/javascript\" async defer src=\"\/\/assets.pinterest.com\/js\/pinit.js\"><\/script>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"mailto:?Subject=Dynamic%20pivoting%20in%20MySQL&Body=Here%20is%20the%20link%20to%20the%20article:%20https:\/\/www.oopsbox.com\/?p=499\" title=\"Email\" class=\"s3-email\"><img decoding=\"async\" src=\"https:\/\/www.oopsbox.com\/wp-content\/plugins\/simple-social-share\/includes\/..\/images\/share-email.png\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t<\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>\u0e04\u0e34\u0e14\u0e16\u0e36\u0e07\u0e23\u0e32\u0e22\u0e07\u0e32\u0e19\u0e04\u0e34\u0e14\u0e16\u0e36\u0e07 Excel 555 &#8230; \u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e04\u0e27\u0e32\u0e21\u0e15\u0e31\u0e49\u0e07\u0e43\u0e08\u0e43\u0e19\u0e01\u0e32\u0e23\u0e40\u0e23\u0e35\u0e22\u0e19 Microsoft Excel \u0e19\u0e35\u0e48\u0e21\u0e35\u0e21\u0e32\u0e2a\u0e31\u0e01\u0e1e\u0e31\u0e01\u0e41\u0e25\u0e49\u0e27 \u0e40\u0e23\u0e34\u0e48\u0e21\u0e14\u0e49\u0e27\u0e22\u0e01\u0e32\u0e23\u0e0b\u0e37\u0e49\u0e2d\u0e2b\u0e19\u0e31\u0e07\u0e2a\u0e37\u0e2d\u0e21\u0e32\u0e01\u0e48\u0e2d\u0e19\u0e41\u0e15\u0e48\u0e19\u0e48\u0e32\u0e08\u0e30\u0e2b\u0e25\u0e32\u0e22\u0e40\u0e14\u0e37\u0e2d\u0e19\u0e25\u0e30 (\u0e08\u0e19\u0e25\u0e37\u0e21\u0e44\u0e1b\u0e40\u0e25\u0e22\u0e27\u0e48\u0e32\u0e0b\u0e37\u0e49\u0e2d\u0e21\u0e32\u0e15\u0e2d\u0e19\u0e44\u0e2b\u0e19 \u0e1a\u0e32\u0e1b\u0e21\u0e32\u0e01) \u0e41\u0e15\u0e48\u0e01\u0e47\u0e19\u0e30\u0e04\u0e27\u0e32\u0e21\u0e02\u0e35\u0e49\u0e40\u0e01\u0e35\u0e22\u0e08\u0e04\u0e23\u0e2d\u0e1a\u0e07\u0e33 \u0e0a\u0e48\u0e27\u0e07\u0e19\u0e35\u0e49\u0e01\u0e47\u0e40\u0e25\u0e22\u0e40\u0e1b\u0e47\u0e19\u0e0a\u0e48\u0e27\u0e07\u0e2d\u0e48\u0e32\u0e19\u0e2b\u0e19\u0e31\u0e07\u0e2a\u0e37\u0e2d Microsoft Excel \u0e41\u0e1a\u0e1a\u0e08\u0e23\u0e34\u0e07\u0e08\u0e31\u0e07 (\u0e41\u0e2d\u0e1a\u0e15\u0e31\u0e49\u0e07\u0e43\u0e08) \u0e40\u0e1b\u0e34\u0e14\u0e14\u0e39\u0e04\u0e23\u0e48\u0e32\u0e27 \u0e46 \u0e21\u0e35\u0e1a\u0e17\u0e19\u0e36\u0e07\u0e1e\u0e39\u0e14\u0e16\u0e36\u0e07\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e17\u0e33 Pivot Table \u0e43\u0e19\u0e42\u0e1b\u0e23\u0e41\u0e01\u0e23\u0e21\u0e15\u0e23\u0e30\u0e01\u0e39\u0e25\u0e15\u0e32\u0e23\u0e32\u0e07\u0e04\u0e33\u0e19\u0e27\u0e13\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e19\u0e35\u0e49\u0e17\u0e38\u0e01\u0e15\u0e31\u0e27\u0e43\u0e19\u0e15\u0e25\u0e32\u0e14\u0e01\u0e47\u0e2a\u0e32\u0e21\u0e32\u0e23\u0e16\u0e17\u0e33\u0e44\u0e14\u0e49 \u0e16\u0e37\u0e2d\u0e40\u0e1b\u0e47\u0e19\u0e2b\u0e19\u0e36\u0e48\u0e07\u0e43\u0e19 Killer Feature \u0e41\u0e15\u0e48\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e19\u0e35\u0e49\u0e41\u0e04\u0e48\u0e1b\u0e23\u0e30\u0e40\u0e14\u0e47\u0e19\u0e19\u0e34\u0e14\u0e2b\u0e19\u0e48\u0e2d\u0e22 ^_^ \u0e2a\u0e33\u0e2b\u0e23\u0e31\u0e1a\u0e04\u0e19\u0e17\u0e35\u0e48\u0e40\u0e02\u0e35\u0e22\u0e19\u0e04\u0e33\u0e2a\u0e31\u0e48\u0e07\u0e40\u0e23\u0e35\u0e22\u0e01\u0e14\u0e39\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 (SQL) \u0e01\u0e47\u0e17\u0e33\u0e40\u0e23\u0e37\u0e48\u0e2d\u0e07\u0e19\u0e35\u0e49\u0e2d\u0e22\u0e39\u0e48\u0e40\u0e19\u0e37\u0e2d\u0e07 \u0e46 \u0e2b\u0e25\u0e32\u0e22\u0e04\u0e23\u0e31\u0e49\u0e07\u0e01\u0e47\u0e17\u0e33\u0e41\u0e1a\u0e1a\u0e2e\u0e32\u0e23\u0e4c\u0e14\u0e04\u0e2d\u0e23\u0e4c \u0e13 \u0e15\u0e2d\u0e19\u0e19\u0e31\u0e49\u0e19\u0e40\u0e25\u0e22 \u0e15\u0e31\u0e27\u0e2d\u0e22\u0e48\u0e32\u0e07\u0e04\u0e23\u0e48\u0e32\u0e27 \u0e46 \u0e2a\u0e21\u0e21\u0e38\u0e15\u0e34\u0e40\u0e23\u0e32\u0e21\u0e35\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e04\u0e48\u0e32\u0e43\u0e0a\u0e49\u0e08\u0e48\u0e32\u0e22\u0e20\u0e32\u0e22\u0e43\u0e19\u0e1a\u0e49\u0e32\u0e19\u0e2a\u0e23\u0e38\u0e1b\u0e1b\u0e23\u0e30\u0e21\u0e32\u0e13\u0e19\u0e35\u0e49 \u0e43\u0e19\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 (\u0e14\u0e49\u0e32\u0e19\u0e0b\u0e49\u0e32\u0e22) \u0e41\u0e25\u0e30\u0e15\u0e49\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e41\u0e2a\u0e14\u0e07\u0e1c\u0e25\u0e40\u0e1b\u0e47\u0e19\u0e2d\u0e35\u0e01\u0e23\u0e39\u0e1b\u0e14\u0e49\u0e32\u0e19\u0e02\u0e27\u0e32 SQL \u0e41\u0e2a\u0e14\u0e07\u0e23\u0e32\u0e22\u0e07\u0e32\u0e19\u0e41\u0e1a\u0e1a\u0e19\u0e35\u0e49 \u0e2d\u0e19\u0e38\u0e21\u0e32\u0e19\u0e08\u0e32\u0e01\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e14\u0e34\u0e1a\u0e17\u0e35\u0e48\u0e40\u0e01\u0e47\u0e1a\u0e43\u0e19\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 \u0e01\u0e48\u0e2d\u0e19\u0e17\u0e35\u0e48\u0e40\u0e23\u0e32\u0e08\u0e30\u0e2a\u0e23\u0e38\u0e1b\u0e44\u0e14\u0e49\u0e01\u0e47\u0e19\u0e48\u0e32\u0e08\u0e30\u0e21\u0e35\u0e1b\u0e23\u0e30\u0e21\u0e32\u0e13\u0e19\u0e35\u0e49 (\u0e40\u0e23\u0e32\u0e43\u0e0a\u0e49\u0e1a\u0e23\u0e34\u0e01\u0e32\u0e23\u0e02\u0e2d\u0e07 generatedata.com) \u0e40\u0e2d\u0e32\u0e15\u0e31\u0e27\u0e2d\u0e22\u0e48\u0e32\u0e07\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e2a\u0e31\u0e01 100 \u0e23\u0e32\u0e22\u0e01\u0e32\u0e23\u0e2b\u0e25\u0e48\u0e30\u0e01\u0e31\u0e19 DROP TABLE IF EXISTS `ExpenseCategory`; &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.oopsbox.com\/?p=499\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Dynamic pivoting in MySQL&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[44],"tags":[54,12,53],"class_list":["post-499","post","type-post","status-publish","format-standard","hentry","category-how-to","tag-crosstab","tag-mysql","tag-pivoting","entry"],"views":4015,"_links":{"self":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/499","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=499"}],"version-history":[{"count":6,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/499\/revisions"}],"predecessor-version":[{"id":515,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/499\/revisions\/515"}],"wp:attachment":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=499"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=499"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=499"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}