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>";

 

[benderulz]

The code you posted at 24 Sep 13 19:31 did exactly what I needed. It properly placed the higher priority values. Thank you so much for all your help!!!