mysql中如何将行值动态转换为列名

vs3odd8k  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(337)

我有一个名为“pivo”的表,其中有列(id、name、course、marks)。我想将“name”列数据转换为行标题,“course”列数据转换为列标题,“marks”列数据转换为从“course”列数据转换的列的行数据。记住在选择查询中忽略“id”列及其数据。
我试过这个:

// Select Students with their Marks Dynamically

$SelectQuery3 = mysqli_query($connection, "SELECT distinct Course FROM pivo");
$numrows3     = mysqli_num_rows($SelectQuery3);
if ( ! $SelectQuery3)
{
    die ("Sorry, database selection failed1. " . mysqli_connect_error());
}
else if ($numrows3 != 0)
{
    echo "  
        <caption><h3>Students with their Marks Dynamically Head</h3></caption>
        <table border = '1' style = 'border-collapse: collapse;'>
            <thead>
                <tr>
                    <th>Name</th>
        ";
    $Crs = "";
    while ($display3 = mysqli_fetch_array($SelectQuery3))
    {
        $Crs = $display3 [0];
        echo "<th>{$display3 ['0']}</th>";
    }
    echo "
                    <th>Total</th>
                    <th>Average</th>
                </tr>
            </thead>
            <tbody>";

    $SelectQuery4 = mysqli_query($connection, "select `Name`,
                sum(case when `Course` = '$Crs' then `Marks` else 0 end) '$Crs'
                from pivo group by `Name`");
    $numrows4     = mysqli_num_rows($SelectQuery4);
    if ( ! $SelectQuery4)
    {
        die ("Sorry, database selection failed2. " . mysqli_connect_error());
    }
    else if ($numrows4 != 0)
    {
        while ($display4 = mysqli_fetch_array($SelectQuery4))
        {
            echo "
                            <tr>
                                <td>{$display4 ['0']}</td>
                                <td>{$display4 ['1']}</td>
                            </tr>";
        }
    }
    else
    {
        echo "<div class='alert alert-info'> Sorry, no student found yet. </div>";
    }

    echo "          
            </tbody>
        </table>";
}
else
{
    echo "<div class='alert alert-info'> Sorry, no course found yet. </div>";
}

得到错误的结果。
有人可以帮忙。
谢谢您。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题