Struggling with pivoting a dataset from rows to columns

I have a dataset that has mutliple records representing different stats for the same entities.

Example:

DEVICE  METRIC           SCORE   WHEN
-------------------------------------------------
devA    reads_per_sec    100    2011-03-01 12:00:00 AM
devA    writes_per_sec   50     2011-03-01 12:00:00开发者_C百科 AM
devA    total_per_sec    150    2011-03-01 12:00:00 AM
devB    reads_per_sec    200    2011-03-01 12:00:00 AM
devB    writes_per_sec   50     2011-03-01 12:00:00 AM
devB    total_per_sec    250    2011-03-01 12:00:00 AM
devC    reads_per_sec    300    2011-03-01 12:00:00 AM
...

I want to pivot everything from this table to a new table that will have this structure

DEVICE  READS  WRITES  TOTAL  WHEN
--------------------------------------------------
devA    100    50      150    2011-03-01 12:00:00 AM
devB    200    50      250    2011-03-01 12:00:00 AM
devC    300    50      150    2011-03-01 12:00:00 AM
...

I have well over a dozen 'metrics' for each of thousands of devices from thousands of timestamps.

It will be far easier to query to see all metric scores for a given device on a given time if I can look at one record for a device for each sample period.

So, my question is, what is the best way to approach this and perform it? I don't mean to ask anyone to do my work for me, but this is beyond my knowledge of databases.

---

This will reproduce the expected result you listed:

  SELECT t.device,
         SUM(CASE WHEN t.metric = 'reads_per_sec' THEN t.score ELSE 0 END) AS reads,
         SUM(CASE WHEN t.metric = 'writes_per_sec' THEN t.score ELSE 0 END) AS writes,
         SUM(CASE WHEN t.metric = 'total_per_sec' THEN t.score ELSE 0 END) AS total,
         t.when
    FROM YOUR_TABLE t
GROUP BY t.device, t.when
ORDER BY t.device, t.when

If you need to deal with the various metrics, you're likely going to have to use dynamic SQL. You'd need to get a unique list of metric values (unless you have a type code table for a foreign key reference):

SELECT DISTINCT t.metric
  FROM YOUR_TABLE t

Then, concatenate the SUM(CASE ... for each metric before executing the query.