php mysql show row data to columns

Question

Hello i need help to show data from row to column in php mysql the original data look like this :

date                bank             total 
----------------------------------------------
2017-02-01           BCA            2500000
2017-02-01           CIMB           1500000
2017-02-01           UOB            3750000
2017-02-02           BCA            2100000
2017-02-02           CIMB           3600000
2017-02-02           UOB            2500000

and what I expected for the output like this one :

date            BCA          CIMB          UOB       TOTAL
----------------------------------------------------------------
2017-02-01    2500000       1500000      3750000    7750000
2017-02-02    2100000       3600000      2500000    8200000

so, if you have any suggestion to make a php mysql code, please help me, thanks

Show us your code, what you've tried so far..and what the problem is. — JustBaron, Feb 20 '17 at 14:37
possible duplicate of - http://stackoverflow.com/questions/7674786/mysql-pivot-table — Veshraj Joshi, Feb 20 '17 at 14:41
Stack-overflow is not a tutorial site, please search php mysql tutorials from google and learn from there. — Masivuye Cokile, Feb 20 '17 at 14:41

score 0 · Accepted Answer · answered Feb 20 '17 at 15:32

Converting rows to columns is called pivot. Most common way to do this is using GROUP BY and functions like MAX or SUM.

Create/insert queries

CREATE TABLE t
    (`date` DATE, `bank` VARCHAR(4), `total` INT)
;

INSERT INTO t
    (`date`, `bank`, `total`)
VALUES
    ('2017-02-01', 'BCA', 2500000),
    ('2017-02-01', 'CIMB', 1500000),
    ('2017-02-01', 'UOB', 3750000),
    ('2017-02-02', 'BCA', 2100000),
    ('2017-02-02', 'CIMB', 3600000),
    ('2017-02-02', 'UOB', 2500000)
;

Query

SELECT
   t.date 
 , MAX(CASE WHEN t.bank = 'BCA' THEN t.total END) AS BCA
 , MAX(CASE WHEN t.bank = 'CIMB' THEN t.total END) AS CIMB
 , MAX(CASE WHEN t.bank = 'UOB' THEN t.total END) AS UOB
 , SUM(t.total) AS total
FROM 
 t
GROUP BY 
 t.date 
ORDER BY 
 t.date ASC

Result

date            BCA     CIMB      UOB  total    
----------  -------  -------  -------  ---------
2017-02-01  2500000  1500000  3750000  7750000  
2017-02-02  2100000  3600000  2500000  8200000

ok, thanks for your code, its very helpfull, and fix my program code, thanks — droid77, Feb 21 '17 at 12:17

php mysql show row data to columns

1 Answers1