Dynamic Pivot, MySQL

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

λ

                                      .NET NoSQL database for rapid development