Created
February 3, 2016 15:33
-
-
Save ptrnov/0bd446f2310aa9f14fcb to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE DEFINER=`root`@`localhost` PROCEDURE `so_1_ver1.1`() | |
| BEGIN | |
| SET @sql = NULL; | |
| SELECT | |
| GROUP_CONCAT(DISTINCT | |
| CONCAT( | |
| "MAX(case when KD_BARANG_ALIAS = '", | |
| KD_BARANG_ALIAS, | |
| "' then SO_QTY end) AS ", | |
| replace(NM_BARANG, ' ', '') | |
| ) | |
| ) INTO @sql | |
| from so_t2; | |
| #SELECT @sql; | |
| SET @sql = CONCAT("SELECT CUST_KD_ALIAS,KD_DIS, ", @sql, " from so_t2"," GROUP BY CUST_KD_ALIAS ", "ORDER BY CUST_KD_ALIAS"); | |
| PREPARE stmt FROM @sql; | |
| EXECUTE stmt; | |
| DEALLOCATE PREPARE stmt; | |
| Table | |
| CREATE TABLE `so_t2` ( | |
| `ID` bigint(20) NOT NULL AUTO_INCREMENT, | |
| `TGL` datetime DEFAULT NULL, | |
| `CUST_KD_ALIAS` varchar(50) DEFAULT NULL, | |
| `KD_DIS` varchar(50) DEFAULT NULL, | |
| `USER_ID` varchar(50) DEFAULT NULL, | |
| `KD_BARANG_ALIAS` varchar(30) DEFAULT NULL, | |
| `NM_BARANG` varchar(255) DEFAULT NULL, | |
| `UNIT_BARANG` varchar(50) DEFAULT NULL, | |
| `UNIT_QTY` decimal(50,2) DEFAULT NULL, | |
| `UNIT_BERAT` decimal(50,2) DEFAULT NULL, | |
| `SO_TYPE` int(11) DEFAULT NULL, | |
| `SO_QTY` decimal(50,2) DEFAULT NULL, | |
| `HARGA_PABRIK` decimal(50,2) DEFAULT NULL, | |
| `HARGA_DIS` decimal(50,2) DEFAULT NULL, | |
| `HARGA_SALES` decimal(50,2) DEFAULT NULL, | |
| `NOTED` text, | |
| `HARGA_LG` decimal(50,2) DEFAULT NULL, | |
| PRIMARY KEY (`ID`) | |
| ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 COMMENT=''; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment