-3
 CREATE TABLE products
    (
        prod_id INT NOT NULL,
        prod_name VARCHAR(50) NOT NULL,
        PRIMARY KEY (prod_id)
    );

    INSERT INTO products (prod_id, prod_name)
    VALUES (1, 'Shoes'), (2, 'Pants'), (3, 'Shirt');

    CREATE TABLE reps
    (
      rep_id INT NOT NULL,
      rep_name VARCHAR(50) NOT NULL,
      PRIMARY KEY (rep_id)
    );


    INSERT INTO reps (rep_id, rep_name)
    VALUES (1, 'John'), (2, 'Sally'), (3, 'Joe'), (4, 'Bob');

    CREATE TABLE sales
    (
      prod_id INT NOT NULL,
      rep_id INT NOT NULL,
      sale_date datetime NOT NULL,
      quantity INT NOT NULL,
      PRIMARY KEY (prod_id, rep_id, sale_date),
      FOREIGN KEY (prod_id) REFERENCES products(prod_id),
      FOREIGN KEY (rep_id) REFERENCES reps(rep_id)
    );

    INSERT INTO sales (prod_id, rep_id, sale_date, quantity)
    VALUES 
      (1, 1, '2013-05-16', 20),
      (1, 1, '2013-06-19', 2),
      (2, 1, '2013-07-03', 5),
      (3, 1, '2013-08-22', 27),
      (3, 2, '2013-06-27', 500),
      (3, 2, '2013-01-07', 150),
      (1, 2, '2013-05-01', 89),
      (2, 2, '2013-02-14', 23),
      (1, 3, '2013-01-29', 19),
      (3, 3, '2013-03-06', 13),
      (2, 3, '2013-04-18', 1),
      (2, 3, '2013-08-03', 78),
      (2, 3, '2013-07-22', 69);

To convert rows to columns query

 $query =  "SET @SQL = NULL;
                                SELECT
                                  GROUP_CONCAT(DISTINCT
                                    CONCAT(
                                      'sum(case when Date_format(s.sale_date, ''%Y-%M'') = ''',
                                      dt,
                                      ''' then s.quantity else 0 end) AS `',
                                      dt, '`'
                                    )
                                  ) INTO @SQL
                                FROM
                                (
                                  SELECT DATE_FORMAT(s.sale_date, '%Y-%M') AS dt
                                  FROM sales s
                                  ORDER BY s.sale_date
                                ) d;

                            SET @SQL 
                              = CONCAT('SELECT r.rep_name, ', @SQL, ' 
                                        from reps r
                                        inner join sales s
                                          on r.rep_id = s.rep_id
                                        inner join products p
                                          on s.prod_id = p.prod_id
                                        group by r.rep_name;');

                            PREPARE stmt FROM @SQL;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;";

query output in sqlyog

rep_name    2013-January    2013-February   2013-March  2013-April  2013-May

Joe 19 0 13 1 0
John 0 0 0 0 20 Sally 150 23 0 0 89

## Above query is executed in php ##

 $output        = mysql_query($query);
            $op         = mysql_fetch_assoc($output);

Its throwing warning

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given;

user2956239
  • 7
  • 7

1 Answers1

0
 $query =  "SET @SQL = NULL;
            SELECT
                GROUP_CONCAT(DISTINCT
            CONCAT(
                'sum(case when Date_format(s.sale_date, ''%Y-%M'') = ''',
                dt,
                ''' then s.quantity else 0 end) AS `',
                dt, '`'
            )
    ) INTO @SQL 
    FROM (
        SELECT DATE_FORMAT(s.sale_date, '%Y-%M') AS dt
        FROM sales s
        ORDER BY s.sale_date
    ) d;
    SET @SQL  = CONCAT('SELECT r.rep_name, ', @SQL, ' 
        from reps r
        inner join sales s
        on r.rep_id = s.rep_id
        inner join products p
        on s.prod_id = p.prod_id
        group by r.rep_name;');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";

Result in mysql

rep_name    2013-January    2013-February   2013-March  2013-April  2013-May    
Joe             19                0             13         1         0  
John             0                0             0          0         20 
Sally           150               23            0          0         89

The Above query executed through PHP CODE

$output     = mysql_query($query);
$op         = mysql_fetch_assoc($output);
print_r($op);

Getting Warning

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given
user2956239
  • 7
  • 7