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

Issue

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

GUYS I Done with my custom code.... check below...

$qry1 = mysql_query("SELECT DISTINCT(`monthyear`) FROM `tbl_sales` ORDER BY `monthyear` ASC");
$rcount = mysql_num_rows($qry1);
$sql2 = "SELECT `plantcode`,`materialcode`, ";
$dynamic_column = array();
while ($rows = mysql_fetch_assoc($qry1)) 
{
    $dynamic_column[] = $rows['monthyear'];
    $sql2 .= "SUM(IF(`monthyear` = '{$rows['monthyear']}',`nrv`,0)) AS '{$rows['monthyear']}'";
    if ($rcount > 1) 
    {
        $sql2 .=',';
    }
    $rcount--;
}

$sql2 .= " FROM `tbl_sales` WHERE `plantcode`='9001' AND `monthyear` BETWEEN '2015-01-01' AND '2016-06-30' GROUP BY `materialcode`,`year` ORDER BY `year`,`monthyear`,`dsapcode` ASC ";

Check below image output screen....

enter image description here

Solution

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 |

Answered By - Blank