List:General Discussion« Previous MessageNext Message »
From:Attila Date:January 22 2009 8:06pm
Subject:Need to pivot rows into columns
View as plain text  
Hi,

I have the following table:

CREATE TABLE DATA (
  TARGET_TIME datetime NOT NULL,
  SCENARIONAME    varchar(20) NOT NULL,
  TOOLNAME varchar(20) NOT NULL,
  STATNAME varchar(100) NOT NULL,
  STATVALUE int(10) NOT NULL,
  PRIMARY KEY (TARGET_TIME, SCENARIONAME, SIMTOOLNAME, STATNAME)
);

I am trying to collect statistics from running tools and feeding them into
the DB.

If I have the following entries:

("2009-01-21 19:34:00", "scenario1", "tool1", "byte_count", 100),
("2009-01-21 19:34:00", "scenario1", "tool1", "udp_count", 200),
("2009-01-21 19:34:00", "scenario1", "tool1", "tcp_count", 300),
("2009-01-21 19:34:00", "scenario1", "tool2", "byte_count", 400),
("2009-01-21 19:34:00", "scenario1", "tool2", "udp_count", 500),
("2009-01-21 19:34:00", "scenario1", "tool2", "tcp_count", 600),

("2009-01-21 19:33:00", "scenario1", "tool1", "byte_count", 10),
("2009-01-21 19:33:00", "scenario1", "tool1", "udp_count", 20),
("2009-01-21 19:33:00", "scenario1", "tool1", "tcp_count", 30),
("2009-01-21 19:33:00", "scenario1", "tool2", "byte_count", 40),
("2009-01-21 19:33:00", "scenario1", "tool2", "udp_count", 50),
("2009-01-21 19:33:00", "scenario1", "tool2", "tcp_count", 60)
(Notice that the "old" targettime  will be ignored in this particular query
as we are only interested in the "latest")

I would like to select only the most recent "targettime" within 1 minute and
only display only the rows that are "the latest" and print out all of the
stats as columns on a per toolname basis:

Targettime                         Scenario        Toolname
byte_count   udp_count     tcp_count
2009-01-21 19:34:00           scenario1       tool1
100             200               300
2009-01-21 19:34:00           scenario1       tool2              400
           500               600

The purpose of the query is to display the "latest statistics" for each
scenario/toolname group.  It is important that the original data comes in
rows (statname may not be known ahead of time, so it must be stored as a
string in a field value).

I am using MySQL and have found some possibilities with SQL Server (Pivot,
crosstab, etc) ... but the requirements call for MySQL.
Is the above query possible?

Thanks a lot!
-- 
Attila
Software Developer
atteeela@stripped

Thread
Need to pivot rows into columnsAttila22 Jan
  • RE: Need to pivot rows into columnsOFT)22 Jan
    • RE: Need to pivot rows into columnsmos22 Jan
  • Re: Need to pivot rows into columnsPeter Brawley23 Jan
    • Re: Need to pivot rows into columnsAttila1 Feb