php - SQL Pivot Query 中的 PHP 数组显示不同的数据

问题描述

我有一个 SQL Server 查询，它在 SQL Server 管理工具中显示以下结果：

   loc    L    M    S   XL
1  001    1    2    0    1
2  006    4    3    0    2
3  012    1    1    1    0
4  022    0    1    0    0

问题 1：为了在网页上显示此结果，我在 PHP 中创建了一个数组，但它显示了更多我不需要的数据（在每个尺寸 (L,M,S,XL) 之后，都有一个额外的数字数量再次显示）。

问题 2：是否可以按照我可以定义的方式对尺寸进行排序？尺寸应从小到大排序，例如：S - M - L - XL，而不是按字母顺序排列。（有时一个项目的尺寸以数字显示，从低到高完美排序，所以没问题。）

Array
(
    [0] => Array
        (
            [Location Code] => 001
            [0] => 001
            [L] => 1
            [1] => 1
            [M] => 2
            [2] => 2
            [S] => 0
            [3] => 0
            [XL] => 1
            [4] => 1
        )

    [1] => Array
        (
            [Location Code] => 006
            [0] => 006
            [L] => 4
            [1] => 4
            [M] => 3
            [2] => 3
            [S] => 0
            [3] => 0
            [XL] => 2
            [4] => 2
        )

    [2] => Array
        (
            [Location Code] => 012
            [0] => 012
            [L] => 1
            [1] => 1
            [M] => 1
            [2] => 1
            [S] => 1
            [3] => 1
            [XL] => 0
            [4] => 0
        )

    [3] => Array
        (
            [Location Code] => 022
            [0] => 022
            [L] => 0
            [1] => 0
            [M] => 1
            [2] => 1
            [S] => 0
            [3] => 0
            [XL] => 0
            [4] => 0
        )

)

SQL查询：

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @item varchar(max);
DECLARE @open varchar(max);
SET @item = 291557
SET @open = 1

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([size])
FROM (SELECT DISTINCT [size] FROM [db].[dbo].[table] WHERE [Item] = @item AND [Open] = @open) AS data

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  'SELECT [Loc], ' + @ColumnName + '
    FROM 
    (SELECT ROUND(CAST([Quantity] AS decimal (2,0)), 1) AS [QuantityRound],  [Loc], [size] FROM [db].[dbo].[table] 
    WHERE [Item] = ''' + @item + ''' AND [Open] = ''' + @open + ''') x
    PIVOT(SUM([QuantityRound])
          FOR [size] IN (' + @ColumnName + '))  AS PVTTable ORDER BY [Loc] + 0 ASC'
--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery

PHP 文件：

$ItemLedgerPivotQuery = "
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);
DECLARE @SelectColumnNames AS NVARCHAR(MAX);

DECLARE @item varchar(max);
DECLARE @open varchar(max);
SET @item = '$ItemNo';
SET @open = 1;

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([size])
FROM (SELECT DISTINCT [size] FROM [db].[dbo].[table] WHERE [Item No_] = @item AND [Open] = @open) AS data

SELECT @SelectColumnNames
    = ISNULL(@SelectColumnNames + ',','')
    + 'ISNULL(' + QUOTENAME([size]) + ', 0) AS '
    + QUOTENAME([size])
    FROM (SELECT DISTINCT [size] FROM [db].[dbo].[table] WHERE [Item No_] = @item AND [Open] = @open) AS data

SET @DynamicPivotQuery = 
  'SELECT [loc], ' + @SelectColumnNames + '
    FROM 
    (SELECT ROUND(CAST([Remaining Quantity] AS decimal (2,0)), 1) AS [quantity],  [loc], [size] FROM [db].[dbo].[table] 
    WHERE [Item No_] = ''' + @item + ''' AND [Open] = ''' + @open + ''') x
    PIVOT(SUM([quantity])
          FOR [size] IN (' + @ColumnName + '))  AS PVTTable ORDER BY [loc] + 0 ASC'

EXEC sp_executesql @DynamicPivotQuery";

$sth = $dbh->prepare($ItemLedgerPivotQuery);
$sth->execute();
$result = $sth->fetchAll();

echo '<table border="1">';
echo '<tr>';
foreach( $result as $key => $value )
{
    if( is_array($value) ) 
    { 
        foreach($value as $key => $column) {
            echo '  <th colspan="1" style="width: 30px">'.$key.'</th>';
        }
    }
    else 
    {
        echo '<th colspan="1">Nothing</th>';
    }
    break;
}
echo '</tr>';
//Data
foreach( $result as $key => $value )
{
echo '<tr>';
    if( is_array($value) ) 
    { 
        foreach($value as $key => $column) {
            echo '<td>'.$column.'</td>';
        }
    }
    else 
    {
        echo '<td>'.$value.'</td>';
    }
echo '</tr>';
}

标签： phpsql-server

php - SQL Pivot Query 中的 PHP 数组显示不同的数据

问题描述

解决方案

推荐阅读