Results in Table Format Instead of Columns?

[josborne]

I am currently working with charting functionality of ExtJS. (you can see an example here: http://www.mototheory.com/countries)  This is working great but it requires that the data be fed to it in columns. For most things this works fine but I am trying to create a chart with data entirely in rows. What I need to do is create a query creates a column for each unique Lap id. 

 

To clarify, I am trying to take a result set that looks like this:

 

 

Rider_ID....Lap................Lap_Total

1................1................109.801

2................1................152.195

3................1................109.249

4................1................110.27

1................2................102.326

2................2................103.704

3................2................102.155

4................2................102.145

1................3................101.723

2................3................102.386

3................3................101.768

4................3................101.569

1................4................102.033

2................4................102.386

3................4................101.941

4................4................101.297

 

 

 

 

And return it like this:

 

 

Rider_ID.......Lap 1..................Lap 2....................Lap 3...................Lap 4

1................109.801................102.326................101.723................102.033

2................152.195................103.704................102.386................102.386

3................109.249................102.155................101.768................101.941

4................110.27..................102.145................101.569................101.297

 

 

I have come up with some clumsy ways to deal with this but the problem I run into is that I won't know the number of laps contained by each race ID so the number of columns could be anywhere from 5 to 40.

 

I have attached  a text file that has teh SQL to create the table and load some of the data (it is a little bit too much to post into this thread)

 

Any help would be greatly appreciated.

 

[attachment deleted by admin]

[PFMaBiSmAd]

See this mysql function - http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[josborne]

IF I am not mistaken, the GROUP_CONCAT() function will display the results correctly but they will all be in a single column. In order for the charting component to understand the data, it must be returned in a seperate column for each lap (does that make sense?).

 

GROUP CONCAT would return this:

 

Rider_ID.......GROUP_CONCAT(Lap)

1................109.801, 102.326, 101.723, 102.033

2................152.195, 103.704, 102.386, 102.386

3................109.249, 102.155, 101.768, 101.941

4................110.27, 102.145, 101.569, 101.297

 

 

When I need this:

 

Rider_ID.......Lap 1..................Lap 2....................Lap 3...................Lap 4

1................109.801................102.326.. ..............101.723................102.033

2................152.195................103.704.. ..............102.386................102.386

3................109.249................102.155.. ..............101.768................101.941

4................110.27..................102.145. ...............101.569................101.297

[PFMaBiSmAd]

You have not exactly provided any information about why the GROUP_CONCAT() output is not what you expect. For all we can tell, all you really need to do is use a a different SEPARATOR character (a space or a tab) instead of a comma.

[josborne]

Sorry. The charting tool I am using uses each column as a seperate set of values and creates a line on the chart for each one. The GROUP_CONCAT() function lists all of the data in a single column which (if the tool could even parse the column as a numerical value) would only provide a single line on the chart.  Essentially, the charting tool doesn't understand or even interpret the seperators. It needs distinct columns.

 

I apologize for this being so confusing. I am trying to make it clear but it seems I am having difficulty.

[fenway]

Then you'll need to join in the table 4 times, and alias columns appropriately.

[josborne]

Then you'll need to join in the table 4 times, and alias columns appropriately.

 

So, I thought of that but, in this case, there could be anywhere from 4 to 35 columns and I won't know how many when running the query.

[fenway]

And that's precisely why you should normalize your tables.

[PFMaBiSmAd]

Results in Table Format

And return it like this:

When I need this:

 

You haven't gotten a solution because your question doesn't contain a definition of what data structure 'this' is. So, far all you have posted are some characters that appear in a post in this Forum, so you have gotten suggestions on how to get the characters to appear like that. Is 'this' a HTML table, an array of arrays, a set of sequentially named variables (please don't say it is), a CSV file (this apparently has been ruled out), an actual EXCEL spreadsheet, or one of several other possible things?

[josborne]

And that's precisely why you should normalize your tables.

 

Wow. I am looking at the table and I cannot fathom how it could be normalized any further. I think that the way I have phrased my question has created too much confusion.

 

 

You haven't gotten a solution because your question doesn't contain a definition of what data structure 'this' is. So, far all you have posted are some characters that appear in a post in this Forum, so you have gotten suggestions on how to get the characters to appear like that. Is 'this' a HTML table, an array of arrays, a set of sequentially named variables (please don't say it is), a CSV file (this apparently has been ruled out), an actual EXCEL spreadsheet, or one of several other possible things?

 

I do apologize for making this so confusing. I am trying to figure out how to clarify what I am looking for.

 

I think that part of my problem is that I don't know how to display the results of a query in a format that people here are used to. I am looking through other topics here to try to improve how I have formatted the results that I am looking for.

[emopoops]

just use the old mysql_result to do it. that s the best way

[josborne]

Hopefully a better attempt at explaining myself.

 

I have a table with this structure:

 

CREATE TABLE `Lap_tbl` (

`Race_ID` int(10) NOT NULL,

`Rider_ID` int(5) NOT NULL,

`Lap` tinyint(4) NOT NULL,

`Lap_Time` time NOT NULL,

`Lap_MSecs` varchar(5) NOT NULL,

`Lap_Total` double default NULL,

`Season` year(4) NOT NULL,

KEY `Rider_ID` (`Rider_ID`),

KEY `Race_ID` (`Race_ID`),

KEY `Season` (`Season`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

The attachment in the first post has the table structure and a sample of the data.

 

 

 

A common query on this table would be:

 

SELECT Rider_ID, Lap, Lap_Total
FROM `Lap_tbl` 
WHERE Rider_ID <5
AND Race_ID =1 

 

Which would yield this result:

 

+----------+-----+------------+
| rider_id | Lap | Lap_Total  |
+----------+-----+------------+
| 1        | 1   | 109.801    |
| 2        | 1   | 152.195    |
| 3        | 1   | 109.249    |
| 4        | 1   | 110.27     |
| 1        | 2   | 102.326    |
| 2        | 2   | 103.704    |
| 3        | 2   | 102.155    |
| 4        | 2   | 102.145    |
| 1        | 3   | 101.723    |
| 2        | 3   | 102.386    |
| 3        | 3   | 101.768    |
+----------+-----+------------+

(truncated)

 

 

However, I need to run a query that provides the same results but outputs them with a column for each Lap:

 

+---------+---------+---------+---------+---------+
| rider_id| Lap 1   | Lap 2   | Lap 3   | Lap 4   |
+---------+---------+---------+---------+---------+
| 1       | 109.801 | 102.326 | 101.723 | 102.033 |
| 2       | 152.195 | 103.704 | 102.386 | 102.386 |
| 3       | 109.249 | 102.155 | 101.768 | 101.941 |
| 4       | 110.27  | 102.145 | 101.569 | 101.297 |
+---------+---------+---------+---------+---------+

 

Essentially, turning the results from a list into a pivot table.

[josborne]

The first thought I had on this was to do a join for each distinct Lap value. The problem with this approach is that I would need to know the number of distinct lap values in order for this approach to work. Additionlly, if there were 35 Lap values, I would be joining 35 results.

[josborne]

I guess there is no "edit post" function.

 

Anyway, another option would be

 

SELECT Rider_ID,
if(Lap=1,Lap_Total,null) as Lap1,
if(Lap=2,Lap_Total,null) as Lap2,
if(Lap=3,Lap_Total,null) as Lap3,
if(Lap=4,Lap_Total,null) as Lap4
from Lap_tbl;

 

 

 

But this, like the join would require that I know the number of Lap values so if results set contained 16 Lap values, the query would look like this:

 

SELECT Rider_ID,
if(Lap=1,Lap_Total,null) as Lap1,
if(Lap=2,Lap_Total,null) as Lap2,
if(Lap=3,Lap_Total,null) as Lap3,
if(Lap=4,Lap_Total,null) as Lap4,
if(Lap=5,Lap_Total,null) as Lap5,
if(Lap=6,Lap_Total,null) as Lap6,
if(Lap=7,Lap_Total,null) as Lap7,
if(Lap=8,Lap_Total,null) as Lap8,
if(Lap=9,Lap_Total,null) as Lap9,
if(Lap=10,Lap_Total,null) as Lap10,
if(Lap=11,Lap_Total,null) as Lap11,
if(Lap=12,Lap_Total,null) as Lap12,
if(Lap=13,Lap_Total,null) as Lap13,
if(Lap=14,Lap_Total,null) as Lap14,
if(Lap=15,Lap_Total,null) as Lap15,
if(Lap=16,Lap_Total,null) as Lap16
from Lap_tbl;

 

 

But if there were 20 Lap values, I wouldn't get the last 4. Theoretically, I could just run that query out to the maximum number of Lap values possible (about 35) and if the Lap values only went to 20, the query would simply return NULL values for the last 15. Not very elegant.

[fenway]

Sorry, I thought your 2nd output block was the table structure -- my bad.

 

This is called a "cross-tab" -- and you can easily pre-generate the query from the max # of laps.

[josborne]

Ahh! Okay. Having a name for it helps a lot. I have found some good information.

 

 

I have realized that the queries I posted above won't work because of the SUM. I am trying to build a query that will.

 

I'm not quite sure how to use the max # of loops though. Any help would be greatly appreciated.

[josborne]

Well, I finally had the chance to get back to this.

 

I really appreciate all the help and patience I have received but I am stuck again.

 

I can easily create the cross-tab query if I know the number of Lap columns I will need:

 

SELECT Rider_ID,
SUM(IF(Lap=1, Lap_Total,0)) AS 'Lap 1',
SUM(IF(Lap=2, Lap_Total,0)) AS 'Lap 2',
SUM(IF(Lap=3, Lap_Total,0)) AS 'Lap 3',
SUM(IF(Lap=4, Lap_Total,0)) AS 'Lap 4',
SUM(IF(Lap=5, Lap_Total,0)) AS 'Lap 5',
SUM(IF(Lap=6, Lap_Total,0)) AS 'Lap 6'
FROM Lap_tbl
WHERE Race_ID=37
GROUP BY Rider_ID

 

In this case, I just had 6 laps

 

However, I am not sure how to create some sort of loop to determine the number of distinct Lap value.

 

I came across a way to do it using a stored procedure to generate the SQL statement. Unfortunately, my current web host doesn't allow creation of stored procedures.

[fenway]

Just run a query beforehand to figure it out, then build the string yourself -- easy as pie.