mysql query for transposing data

I need to transform the values in my column into different columns.

Example data with me currently:

id  dtime         std_time   ifInOctets
-----------------------------------------------------
4   1279027201    13:20:01   1.34E+08    
1   1279027201    13:20:01   34562    
2   1279027201    13:20:01   9.72E+07    
4   1279027201    13:21:02   8614    
1   1279027201    13:21:04   13200

What I want to do is:

id  input-09:00   input-09:01
------------------------------------
1        3869   4           371    
2        2317                2160    
6        2234               2073    
7        3643                2736    
8        3230                3565    
9         3072       2944

The above sample data do not have same numbers ( please ignore that )

The query I am currently using is:
SELECT
r.name
  , r.namestring
  , i.name
  , r.rid
  , i.id
  , d.dtime
  , d.ifInOctets
FROM rout AS r
INNER JOIN interface AS i ON r.rid = i.rid
INNER JOIN 1278993600_1_60 AS d ON i.id = d.id
AND dtime BETWEEN 1279039800 AND 1279050000
AND i.status = "active"

*I have not added all the values in my sample input. just brief. * ifInOctet and input is the same and time is in unixtimestamp

I am not understanding how I could transpose the information in my mysql query. I want the time to be in columns : 9:01, 9:02 and so on with its values. Each id has 1 value for every minute but can appear in different seconds: 9:02:01, 9:02:02 which needs to be considered.

Please help. I am not sure how I can go about transposing it and there are over million records to process.

ansaurus

tags:

views:

answers:

mysql query for transposing data

related questions