i have some table scores like this...
id A B score
1 10 4 100
2 10 2 320
3 10 1 100
4 20 4 20
5 20 3 100
6 20 2 120
7 20 1 110
8 30 4 30
9 30 3 200
and i want some query or php method to make output like
column A => 10 20 30
4 = 100 20 30
3 = null 100 200
2 = 320 120 null
1 = 100 110 null
^
||
(coumn b)
or
4 ,100,20,30
3 ,null,100,200
2 ,320,120,null
1 ,100,110,null
so i want get column score but based on coumn B, if column A don't have a row score of one column B, will give null.
i have tried full join, cross join, etc but i failed to get that's like it.
SELECT * FROM TABLE CROSS JOIN
(SELECT * FROM TABLE CROSS GROUP BY B)
ORDER BY B DESC
thanks
Here is another way you can do it doing a nested loop (using mysqli_*):
echo '<table>';
$stmt = $connection->prepare("SELECT B FROM TABLE ORDER BY B DESC");
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($b);
while($stmt->fetch()){
echo '<tr>
<td>'.$b.'</td>';
$stmt2 = $connection->prepare("SELECT A FROM TABLE GROUP BY A ORDER BY A");
$stmt2->execute();
$stmt2->store_result();
$stmt2->bind_result($a);
while($stmt2->fetch)){
$stmt3 = $connection->prepare("SELECT score FROM TABLE WHERE A = ? AND B = ?");
$stmt3->bind_param("ii", $a, $b);
$stmt3->execute();
$stmt3->store_result();
if($stmt3->num_rows > 0){
$stmt3->bind_result($score);
$stmt3->fetch();
echo '<td>'.$score.'</td>';
} else {
echo '<td>NULL</td>';
}
$stmt3->close();
} /* END OF WHILE LOOP OF SECOND STATEMENT */
$stmt2->close();
echo '</tr>'; /* END OF ROW */
} /* END OF WHILE LOOP */
$stmt->close();
echo '</table>';
But I'm pretty sure someone would answer your question in a single query.