Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Dynamic Pivot
######################################################### ## DDL & DML ## ######################################################### DROP TABLE IF EXISTS `Order`; CREATE TABLE `Order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `NO_PO` varchar(6) NOT NULL, `NAMA` varchar(50) NOT NULL, `QTY` int(11) NOT NULL, primary key (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Order` (`NO_PO`, `NAMA`, `QTY`) VALUES ('PO1234', 'Buku', 1), ('PO1234', 'Pulpen', 5), ('PO1234', 'Pensil', 5), ('PO5678', 'Buku', 4), ('PO5678', 'Pulpen', 6), ('PO5678', 'Pensil', 10); ######################################################### ## Tabel Awal ## ######################################################### SELECT `NO_PO`, `NAMA`, `QTY` FROM `Order`; ######################################################### ## Tabel Akhir ## ## (Static Pivot) ## ######################################################### SELECT NAMA, MAX(IF(NO_PO = 'PO1234', QTY, NULL)) AS PO1234, MAX(IF(NO_PO = 'PO5678', QTY, NULL)) AS PO5678, SUM(QTY) `Grand Total` FROM `Order` GROUP BY NAMA; ######################################################### ## Tabel Akhir ## ## (Dynamic Pivot) ## ######################################################### SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(NO_PO = ''', NO_PO, ''', QTY, NULL)) AS ', NO_PO ) ) INTO @sql FROM `Order`; SET @sql = CONCAT(' SELECT NAMA,', @sql, ',SUM(QTY) `Grand Total` FROM `Order` GROUP BY NAMA '); PREPARE stmt1 FROM @sql; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; ######################################################### ## Tabel Akhir ## ## (Static Pivot with Get Total in Last Row use Union) ## ######################################################### (SELECT NAMA, MAX(IF(NO_PO = 'PO1234', QTY, NULL)) AS PO1234, MAX(IF(NO_PO = 'PO5678', QTY, NULL)) AS PO5678, SUM(QTY) `Grand Total` FROM `Order` GROUP BY NAMA) UNION (SELECT 'Grand Total' AS NAMA, SUM(IF(NO_PO = 'PO1234', QTY, NULL)) AS PO1234, SUM(IF(NO_PO = 'PO5678', QTY, NULL)) AS PO5678, SUM(QTY) `Grand Total` FROM `Order`); ########################################################## ## Tabel Akhir ## ## (Dynamic Pivot with Get Total in Last Row use Union) ## ########################################################## SET @sql1 = NULL; SET @sql2 = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(NO_PO = ''', NO_PO, ''', QTY, NULL)) AS ', NO_PO ) ) INTO @sql1 FROM `Order`; SET @sql1 = CONCAT(' (SELECT NAMA,', @sql1, ',SUM(QTY) `Grand Total` FROM `Order` GROUP BY NAMA) '); SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(IF(NO_PO = ''', NO_PO, ''', QTY, NULL)) AS ', NO_PO ) ) INTO @sql2 FROM `Order`; SET @sql2 = CONCAT( @sql1, 'UNION (SELECT \'Grand Total\' AS NAMA,', @sql2, ',SUM(QTY) `Grand Total` FROM `Order`) '); PREPARE stmt2 FROM @sql2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2; ########################################################## ## Tabel Akhir ## ## (Static Pivot with Get Total in Last Row use Rollup) ## ########################################################## SELECT NAMA, SUM(IF(NO_PO = 'PO1234', QTY, NULL)) AS PO1234, SUM(IF(NO_PO = 'PO5678', QTY, NULL)) AS PO5678, SUM(QTY) `Grand Total` FROM `Order` GROUP BY NAMA WITH ROLLUP; ########################################################### ## Tabel Akhir ## ## (Dynamic Pivot with Get Total in Last Row use Rollup) ## ########################################################### SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(IF(NO_PO = ''', NO_PO, ''', QTY, NULL)) AS ', NO_PO ) ) INTO @sql FROM `Order`; SET @sql = CONCAT(' SELECT NAMA,', @sql, ',SUM(QTY) `Grand Total` FROM `Order` GROUP BY NAMA WITH ROLLUP '); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
run
|
edit
|
history
|
help
0
daily score
Newspaper Site
Siva7
1
ITM_14210069
mysql - where (between)
MySQL : calculate overlapping periods
Charles Chung
Test for MySQL "serializable" transaction, part 1
Stackoverflow Problem Solve : how to write a mysql query based on the output?