How to rearrange the following data using php?
i retrieved the following data using sql query from mysql
TOTAL COMPUTER DATE GROUP
-----------------------------------------------
48 LAPTOP2 2009-08-19 1
77 LAPTOP2 2009-08-20 1
0 LAPTOP2 2009-08-21 1
15 LAPTOP1 2009-08-19 1
25 MAIN 2009-08-23 1
25 LAP3 2009-08-18 2
3 LAP3 2009-08-19 2
55 LAP3 2009-08-20 2
i would like to rearrange the data like using php
group computer 2009-08-18 2009-08-19 2009-08-20 2009-08-21 2009-08-22 2009-08-23
------------------------------------------------------------------------------------------------
1 LAPTOP2 0 48 77 0 0 0
1 LAPTOP1 0 15 0 0 0 0
开发者_JS百科1 MAIN 25
2 LAP3 25 3 55 0 0 0
Use the following query to pivot the data:
SELECT t.group,
t.computer,
MAX(CASE WHEN t.date = '2009-08-18' THEN t.total ELSE 0 END) AS '2009-08-18',
MAX(CASE WHEN t.date = '2009-08-19' THEN t.total ELSE 0 END) AS '2009-08-19',
MAX(CASE WHEN t.date = '2009-08-20' THEN t.total ELSE 0 END) AS '2009-08-20'
--, etc...
FROM TABLE t
GROUP BY t.group, t.computer
Your options are either to define each column for the data you are pivoting, or you can use MySQL's Prepared Statement syntax to dynamically create those columns.
I feel the need to point out that your example is inconsistent - for LAPTOP2
, you have zero as the value for 2009-08-18
, but the main
value for that is blank. Neither have a record for that date. If you want these to show as blank/etc, change ELSE 0 END
to ELSE NULL END
in the CASE
statements.
精彩评论