How do I change this to a crosstab? 1

[benderulz]

Hello All,

I have a query that works and is returning accurate data, but I want to display it in a different format. Basically, what I need help with is making this into a crosstab query. My query returns:

Zone | Priority | Trans
A | 2 | 50
A | 3 | 10
B | 2 | 5

But, I need the results to look like this:

Priority | A | B |
2 | 50 | 5 |
3 | 10 |

Any guidance is greatly appreciated. The code I have is displayed below.


$sql = "SELECT zone, priority, Count(id) AS trans
FROM Transactions
WHERE priority > '0'
GROUP BY zone,priority
ORDER BY zone, priority";

$r = odbc_exec($conn,$sql);

echo "<table border='1' cellpadding='10' align='center' bgcolor='gray'>

<tr>
<th bgcolor='6495ED'>ZONE</th>
<th bgcolor='6495ED'>Priority</th>
<th bgcolor='6495ED'>Trans</th>
</tr>";



while (odbc_fetch_row($r)){

echo "<tr>";
echo"<td bgcolor='white' align='center'>".odbc_result($r,"zone")."</td>";
echo"<td bgcolor='white' align='center'>".odbc_result($r,"priority")."</td>";
echo"<td bgcolor='white' align='center'>".odbc_result($r,"trans")."</td>";
echo"</tr>";
}
echo "</table>";

 

[jpadie]

could you have another go and describing the desired output? the number of columns per row do not match, and there seems to be a column heading in a row, which is rather odd.

by the by, remember always to post code within [ignore]

[/ignore] tags.

 

[benderulz]

Does this make more sense?

Current code displays this:

Zone | Priority | Trans
A    |    2     | 50
A    |    3     | 10
B    |    2     | 5

But, I need the results to look like this:

[tab]   | A  | B | 
   2   | 50 | 5 |
   3   | 10 |

The Zones would be my column headers, Priorities would be the row heading, and Trans would be the value.

 

[jpadie]

urgh ... yes, I see that. off the top of my head that is difficult to do neatly in sql (and you don't say what rdbms you are using). but should be easy enough to do in php. it is a computationally expensive operation though. is this something that is likely to be done across large datasets and frequently?

 

[jpadie]

although it may be possible to do in raw sql if there really are only two zones. or a small number of zones.

 

[jpadie]

here you go

<?php

/* establish holding variable */
$rows = array();
$rows[0] = array('priority');

while (odbc_fetch_row($r)){
	foreach (array('zone','priority','Trans') as $field ) $$field = odbc_result($r, $field);	
	/*	create the zone column headings */
	if(!in_array($zone, $rows[0])) $rows[0][]= $zone; 
	/*	find the right column to put the Trans value in */
	$col = array_keys( $rows[0], $zone);
	/*	create the row if we hit a new priority */
	if(!isset($rows[$priority])) $rows[$priority] = array($priority);
	/* 	insert the Trans  value in the right column of the right row */	
	$rows[$priority][$col[0]] = $Trans;
endforeach;
/* reset the array */
$rows = array_values($rows);
print_r( $rows );

?>

 

[benderulz]

First of all, THANK YOU for your help! I am new to using php, so forgive me if my questions sound stupid.

It would be run multiple times throughout the day, and it is running against an informix database. When I run my query, it returns 35 zones with 511 transactions and 4 priorities. This would vary based on business and warehouse location.

As far as placement, your code would live after my odbc_exec? Does this still require me to define the <tr> and <th> or does it simply print the array rows in a default format?

 

[jpadie]

ok. that does not sound terrible. you've only got 35 zones and 4 priorities so the task is not material.

the print_r() command just dumps the array in a structure fashion. if you wanted the output in a table then you'd do this instead of the print_r(). yes - everything comes after the odbc_exec

echo '<table>';
echo '<thead><tr><th>' . implode('</th><th>', $rows[0]) . '</th></tr></thead>';
echo '<tbody>';
foreach($rows as $key=>$row):
 if($key == 0) continue;
 echo "<tr><td bgcolor='white' align='center'>" . implode("</td><td bgcolor='white' align='center'>", $row) .'</td></tr>';
endforeach;
echo '</tbody></table>';

I have included your style declarations inline to the <td> tags, but it would be better if you used css rather than inline styles.

 

[benderulz]

I'm fine with dumping the array, but I keep getting a HTTP 5000 Internal Server Error, and I thought it might have been caused by the way I was attempting to display the results.

 

[jpadie]

nope. that's a sign that there is a syntax error in the text.

which is my bad ...

change this line

while (odbc_fetch_row($r)){

to this

while(odbc_fetch_row($r)):

and change this line

endforeach;

to

endwhile;

the mistakes arose because I used a stubbed out array as a data source.

 

[benderulz]

The query runs, but when we change priorities it does not skip the zone if it does not have a value. For example, the code is returning:

Priority  |  A   |  B  |  C  |
1         |  10  |  5  |  2  |
2         |   1  |     |     |
3         |   1  |     |     |

But it should of returned:

Priority  |  A   |  B  |  C  |
1         |  10  |  5  |  2  |
2         |      |  1  |     |
3         |      |     |  1  |

 

[jpadie]

hmm. i'm not sure why that might happen. the code does not look wrong.

here is a slightly tidied version. can you try this and if it does not work can you provide a sample dataset in sql compliant dump format?

<?php

$in = array(
	array('zone'=>'A', 'priority'=>2, 'Trans'=>50),
	array('zone'=>'A', 'priority'=>3, 'Trans'=>10),
	array('zone'=>'B', 'priority'=>2, 'Trans'=>5)
);

/* establish holding variable */
$rows = array();
$rows[0] = array('priority');

while (odbc_fetch_row($r)):
	foreach (array('zone','priority','Trans') as $field ):
              $$field = odbc_result($r, $field);
	endforeach;
	/*	create the zone column headings */
	if(!in_array($zone, $rows[0])):
		$cols[$zone] = count($rows[0]);
		$rows[0][]= $zone;
	endif;
	
	/*	create the row if we hit a new priority */
	if(!isset($rows[$priority])) $rows[$priority][] = $priority;
	
	/* 	insert the Trans  value in the right column of the right row */
	$rows[$priority][$cols[$zone]] = $Trans;
endwhile;
echo '<table>';
foreach($rows as $key=>$row):
	if($key == 0):
 		echo '<thead><tr><th>' . implode('</th><th>', $row) . '</th></tr></thead>';
 		echo '<tbody>';
 	else:
 		echo "<tr><td bgcolor='white' align='center'>" . implode("</td><td bgcolor='white' align='center'>", $row) .'</td></tr>';
 	endif;
endforeach;
echo '</tbody></table>'; 

?>

 

[jpadie]

ignore the $in variable. it is just there for stubbing the input data. delete it if you want.

 

[benderulz]

The new code ran with the same results. It writes the first priority line correctly, but does not position the additional priority values correctly. I'm not sure what you mean by "sql compliant dump format"

 

[benderulz]

If I use the

print_r($rows)

to display the array the column numbers match for the different priority rows (but it does not display in a user friendly format). Does the echo table not know how to handle the column numbers?

 

[jpadie]

The display logic just flattens the array. It does not apply any validation. And it should not need to as the query together with the array building should be enough. Unless Informix is returning null instead of zero for counts on a null record set. In which case either the problem can be fixed in the query or we can apply some more logic in the array builder.
I will post one or both solutions in the morning (it past 1 am in my time zone )

 

[jpadie]

actually, that was lazy of me. here is some code that should work. not checked as I don't have any test data.

<?php
/* establish holding variable */
$rows = array();
$rows[0] = array('priority');

while (odbc_fetch_row($r)):
	foreach (array('zone','priority','Trans') as $field ) $$field = odbc_result($r, $field);
	
	/*	create the zone column headings */
	if(!in_array($zone, $rows[0])):
		$rows[0][]= $zone;
	endif;
	
	/*	create the row if we hit a new priority */
	if(!isset($rows[$priority])) $rows[$priority]['priority'] = $priority;
	
	/* 	insert the Trans  value in the right column of the right row */
	$rows[$priority][$zone] = $Trans;
endwhile;
echo '<table>';
foreach($rows as $key=>$row):
	if($key == 0):
		echo '<thead><tr><th>' . implode('</th><th>', $rows[0]) . '</th></tr></thead>';
		echo '<tbody>';
	else:
		echo '<tr>';
		foreach($rows[0] as $field):
			echo "<td bgcolor='white' align='center'>";
			echo isset($row[$field]) ? $row[$field] : 0;
			echo '</td>';
		endforeach;
		echo '</tr>';
	endif;
endforeach;
echo '</tbody></table>';
?>

 

[jpadie]

a SQL alternative for Informix should be this (all values will be given but you will still need to create the pivot using php

$sql = "
SELECT    
	t.priority,
        t2.zone,
	(
			SELECT 		count(*) 
			FROM 		Transactions 
			WHERE 		priority = t.priority 
			AND		zone = t2.zone
	) as Trans
FROM    (
			SELECT 		priority 
			FROM		Transactions 
			GROUP BY 	priority
	) t
JOIN	(
			SELECT  	zone 
			FROM 		Transactions 
			GROUP BY	zone
	) t2
GROUP BY t2.zone, t.priority


";

I think, anyway. I have not had much experience with Informix.

 

[benderulz]

I will try these as soon as a get back in the office tomorrow. Thank you so much.

 

[jpadie]

If you really do only have a small number of priority levels (numbered 1 to 4) then this query (might) give you what you want directly as a pivot table.

SELECT    
	t.zone,
        sum( case t.priority when 1 then 1 else 0 end) Priority_1,
        sum( case t.priority when 2 then 1 else 0 end) Priority_2,
        sum( case t.priority when 3 then 1 else 0 end) Priority_3,
        sum( case t.priority when 4 then 1 else 0 end) Priority_4
FROM Transactions t
GROUP BY t.zone ASC

If you have an arbitrary number of priorities then you would need to do this in two stages, first getting the available priorities and then building a query from them. Let us know if that is something you would need. In MySql this would be best handled by a stored procedure; but I have no idea whether such things are available in Informix; nor their syntax.

I do see that the output of the above query is using the priorities as columns, which makes sense visually as you say there is only four of them, whereas there are 35 zones. So from a UI perspective it would be better to have the priorities as columns and the zones as rows. However I also see that you asked for the other way around. For this you would have either to hard code the columns, use prepared statements, use multiple queries or pivot the rows and columns in php after the query (in which case you save little if anything over the original solution).

$fields = array('zone','priority_1','priority_2','priority_3','priority_4');
$first = true;
echo '<table>';
while (odbc_fetch_row($r)):
	foreach ( $fields as $field ) $$field = odbc_result($r, $field);	
	if($first):
		echo '<thead><tr><th>' . implode('</th><th>', $fields) . '</th></tr></thead>';
		echo '<tbody>';
		$first = false;
	endif;
	echo "<tr>";
	foreach ($fields as $field) echo "<td bgcolor='white' align='center'>${$field}</td>";
	echo "</tr>";
endwhile;
echo '</tbody></thead>';