MySql convert data group by date Rows into dynamic columns

Question

I need to know how to do the following thing into mysql let me give you the example as below.

Current Table data

ID, Address, Price, Date
1, ABC, 25$, 2013-10-20
2, ABC1, 35$, 2013-10-20
3, ABC2, 45$, 2013-10-20
4, ABC3, 55$, 2013-10-20
5, ABC4, 65$, 2013-10-20
6, ABC, 25$, 2013-10-21
7, ABC1, 35$, 2013-10-21
8, ABC2, 25$, 2013-10-21
9, ABC3, 115$, 2013-10-21
10, ABC4, 65$, 2013-10-21
11, ABC, 25$, 2013-10-22
12, ABC1, 35$, 2013-10-22
13, ABC2, 345$, 2013-10-22
14, ABC3, 255$, 2013-10-22
15, ABC4, 65$, 2013-10-24

on daily basis date wise data is stored into the table, i want to generate a query to give me data like as follow.

Address, 2013-10-20, 2013-10-21, 2013-10-22
ABC    , 25$       , 25$       , 25$
ABC1   , 35$       , 35$       , 35$
ABC2   , 45$       , 25$       , 345$
ABC3   , 55$       , 115$      , 225$
ABC4   , 65$       , 65$       , 25$

I Want on daily basis a new column added to the above data so that i can generate the report on it. If there is some solution with PHP as well pleaes refer too.

possible duplicate of [MySQL dynamic-pivot](http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot) — Raphaël Althaus, Oct 24 '13 at 10:50
Dear can you give me example as i am new so i am not understanding the example [MySQL dynamic-pivot](http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot) @RaphaëlAlthaus — user2915328, Oct 24 '13 at 10:58
Dear @DipeshParmar yes i have a look of GROUP_CONCAT function but it concat it in one column. i need multiple columns. — user2915328, Oct 24 '13 at 11:00

score 0 · Answer 1 · answered Nov 27 '15 at 04:44

0

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Date = ''',
      Date,
      ''' then Price end) AS `', Date, '`' )
  ORDER BY Date ) INTO @sql
FROM Table;
SET @sql = CONCAT('SELECT Address, ', @sql, ' FROM Table GROUP BY Address');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Nov 27 '15 at 04:44

Maman Sulaeman

11
1

Please add some description to support the piece of code – Divya MV Nov 27 '15 at 05:04

score 0 · Answer 2 · edited Jun 29 '16 at 06:16

You can use this code which has been created by me.

SET @sql = NULL;
    select GROUP_CONCAT(DISTINCT
               CONCAT(' ROUND(SUM(CASE WHEN DATE_FORMAT(Date, ''%b %y'')=''',
                       DATE_FORMAT(Date, '%b %y'),
                       ''' THEN Price ELSE 0 END)) AS ''', 
                       DATE_FORMAT(Date, '%b %y'), '''' 
                     )  
                  ORDER BY Date)
                  INTO @sql
    from Table ; 
    SET @sql = CONCAT('SELECT Address, ', @sql, ' FROM Table GROUP BY Address');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

MySql convert data group by date Rows into dynamic columns

2 Answers2