[Solved] php mysql show row data to columns [closed]

by


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

1

solved php mysql show row data to columns [closed]