开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜