Amit Amit - 5 months ago 6
SQL Question

MySql query gives different output from phpmyadmin, here group_concat not work

Is there any mistake in mysql query, anyone help please.
Thanks!!

DATABASE Table (Below)

CREATE TABLE tbl_sales
(`plantcode` varchar(200), `materialcode` varchar(200), `nrv` varchar(200) ,`monthyear` date)
;
INSERT INTO tbl_sales
(`plantcode`, `materialcode`, `nrv`, `monthyear`)
VALUES
('9001','800000006','210','2015-02-01'),
('9001','800000006','220','2015-02-01'),
('9001','800000006','350','2015-02-01'),
('9001','800000006','100','2015-03-01'),
('9001','800000006','421.00','2015-04-01'),
('9001','400000100','257','2015-05-01'),
('9001','400000100','233','2015-07-01'),
('9002','800000006','710','2016-02-01'),
('9002','800000006','325','2016-06-01')
;


I create query for dynamic row convert into dynamic column by date(monthyear field).

SELECT plantcode,materialcode, GROUP_CONCAT(DISTINCT
CONCAT('SUM(CASE WHEN DATE_FORMAT(monthyear,''%b %y'')= ''',
DATE_FORMAT(monthyear,'%b %y'),
''' THEN nrv ELSE 0 END) AS '' ',
DATE_FORMAT(monthyear, '%b %y'),'"'
)
ORDER BY monthyear)
from tbl_sales
GROUP BY materialcode


when i use this query in phpmyadmin, it shows optput like below

Output From PHPMyadmin

And i need actual output like below:

Plantcode Materialcode Feb 2015 Mar 2015 Apr 2015 May 2015
9001 800000006 780 100 421.00 257

Answer

You can try something like this, cause you use materialcode for group by, so plantcode in result makes no sense.

SQL Fiddle

MySQL 5.6:

CREATE TABLE tbl_sales
    (`plantcode` varchar(200), `materialcode` varchar(200), `nrv` varchar(200) ,`monthyear` date)
;   
INSERT INTO tbl_sales
    (`plantcode`, `materialcode`, `nrv`, `monthyear`)
VALUES
    ('9001','800000006','210','2015-02-01'),
    ('9001','800000006','220','2015-02-01'),
    ('9001','800000006','350','2015-02-01'),
    ('9001','800000006','100','2015-03-01'),
    ('9001','800000006','421.00','2015-04-01'),
    ('9001','400000100','257','2015-05-01'),
    ('9001','400000100','233','2015-07-01'),
    ('9002','800000006','710','2016-02-01'),
    ('9002','800000006','325','2016-06-01')
;

Query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(monthyear = ''',
      monthyear,
      ''', nrv, 0)) AS `',
      DATE_FORMAT(monthyear,'%b %y'), '`'
    )
  ) INTO @sql
FROM tbl_sales;
SET @sql = CONCAT('SELECT plantcode, materialcode, ', @sql, ' FROM tbl_sales GROUP BY materialcode');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Results:

| plantcode | materialcode | Feb 15 | Mar 15 | Apr 15 | May 15 | Jul 15 | Feb 16 | Jun 16 |
|-----------|--------------|--------|--------|--------|--------|--------|--------|--------|
|      9001 |    400000100 |      0 |      0 |      0 |    257 |    233 |      0 |      0 |
|      9001 |    800000006 |    780 |    100 |    421 |      0 |      0 |    710 |    325 |