SQL中PIVOT的PHP脚本

Question

I have a View Emp_PRJ_Art in my SQL Server as below.

UsedArticleNo | EmpName | Project | EmpRol  
PART0001    |    Tintin | PRJ1    |   PL  
PART0002    |   Haddok  | PRJ2    |   TL  
PART0003    |   Poppey  | PRJ3    |   GET  
PART0004    |   Archie  | PRJ4    |   PM  
PART0005    |   Tintin  | PRJ1    |   PL  
PART0006    |   Archie  | PRJ4    |   PM  
PART0007    |   Tintin  | PRJ3    |   PL  
PART0008    |   Haddok  | PRJ3    |   TL  
PART0009    |   Poppey  | PRJ1    |   GET  

I want to get a result like this. The numbers under PRJ columns are total number of Article used by the employee for that particular Project.

EmpName |PRJ1 | PRJ2 |  PRJ3 | PRJ4 |  EmpRol  
Archie  | 0   |  0   |   0   |  2   |  PM  
Haddok  | 0   |  1   |   1   |  0   |  TL  
Poppey  | 1   |  0   |   1   |  0   |  GET  
Tintin  | 2   |  0   |   1   |  0   |  PL  

I used the SQL query like this and getting the result as required.

SELECT *   
FROM ( SELECT EmpName, UsedArticleNo, Project  
        FROM Emp_PRJ_Art)  
PIVOT(COUNT(UsedArticleNo) FOR (Project) IN ('PRJ1','PRJ2','PRJ3',’PRJ4’))  
ORDER BY EmpName;

Now my problem is, I am not able to use PHP to get this query run and finally display the same table as webpage, as I am getting in SQL Developer Console. Below is the PHP code not working.

    <?php

$backcol="bgcolor=#9fffa1"; // Light Green
$colgreen1="bgcolor=#5EE060"; // Light Green

$maxitems=1000; // Maximum items found to display

$s=" <small> ";

$user='odbc';
$pass='abcd';
$sid ='server1.myComp.com';

$adr = "http://server1/WebEditor";

$conn = oci_connect($user, $pass, $sid);
if (!$conn) {
    echo "Unable to connect: " .  var_dump(OCIError()  );
    die();
}

}

$cmd="SELECT * 
FROM ( SELECT EmpName, Used ArticleNo, Project  FROM Emp_PRJ_Art)
PIVOT(COUNT(ArticleNo) FOR (Project) IN ('PRJ1','PRJ2','PRJ3','PRJ4'))
ORDER BY Emp Name";
$stid = oci_parse($conn,$cmd); oci_execute($stid);
echo     "<tr>
            <th $backcol> $s Employee Name
            <th $backcol> $s Project-1
            <th $backcol> $s Project-2
            <th $backcol> $s Project-3
            <th $backcol> $s Project-4
            <th $backcol> $s EmpRol
             ";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {

    echo "<tr>
";
    echo "<td> $s " .$row['EmpName']
        ."<td> $s " .$row['PRJ1']
        ."<td> $s " .$row['PRJ2']
        ."<td> $s " .$row['PRJ3']
        ."<td> $s " .$row['PRJ4']
        ."<td> $s " .$row['EmpRol']
        ;
} 
echo "</table>
";
echo "<br><font color='blue'>This is a test</font>";
exit;
?>

The EmpName has 700 entries and Project has 70 entries. UsedArticle can be more than 10k.

Answer 1

There are numerous errors in your code that stop it working correctly. I've modified it as follows and tested it successfully against my setup (Oracle 11gR2, PHP 5.6.x). Most important is aliasing the IN values so they aren't referenced with surrounding single quotes '.

<?php
$backcol = "bgcolor=#9fffa1"; // Light Green    
//$colgreen1 = "bgcolor=#5EE060"; // Light Green

//$maxitems = 1000; // Maximum items found to display

$s=" <small> ";

$user = 'odbc';
$pass = 'abcd';
$sid = 'server1.myComp.com';

//$adr = "http://server1/WebEditor";

$conn = oci_connect($user, $pass, $sid);
if (!$conn) {
    echo "Unable to connect: " .  var_dump(OCIError());
    die();
}

//}

$cmd = "SELECT *
FROM ( SELECT EmpName, UsedArticleNo, Project, EmpRol  FROM Emp_PRJ_Art)
PIVOT(COUNT(UsedArticleNo) FOR (Project) IN ('PRJ1' as prj1, 'PRJ2' as prj2, 'PRJ3' as prj3, 'PRJ4' as prj4))
ORDER BY EmpName";

$stid = oci_parse($conn,$cmd);
oci_execute($stid);

echo     "<table>
          <tr>
            <th $backcol> $s Employee Name </th>
            <th $backcol> $s Project-1 </th>
            <th $backcol> $s Project-2 </th>
            <th $backcol> $s Project-3 </th>
            <th $backcol> $s Project-4 </th>
            <th $backcol> $s EmpRol </th>
          </tr>";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {

    echo "<tr>
";
    echo "<td> $s " . $row['EN'] . "</td>"
        ."<td> $s " . $row['PRJ1'] . "</td>"
        ."<td> $s " . $row['PRJ2'] . "</td>"
        ."<td> $s " . $row['PRJ3'] . "</td>"
        ."<td> $s " . $row['PRJ4'] . "</td>"
        ."<td> $s " . $row['ER'] . "</td>"
        ."</tr>"
    ;
}
echo "</table>
";