sql - 数据透视表返回带有 NULL 的多行，结果应分组在一行上

Question

我有下面的表格，我希望对其进行透视，以便第 1 列中的描述成为新透视中的列标题。

 Nominal Group  | GrpID | Description     | Value       | CustomerID
 ---------------+-------+-----------------+-------------+-----------
 Balance Sheet  | 7     | BS description  | 56973.10    |     2
 Cost of Sales  | 4     | COS description | 55950.17    |     2
 Sales          | 1     | Sales           | -178796.18  |     2
 Labour Costs   | 5     | Wages           | 18596.43    |     2
 Overheads      | 6     | Rent            | 47276.48    |     2

我使用下面的代码来获取下面的结果集:

select * from trialbalancegrouping 
PIVOT (Sum(value)
for nominalgroupname in ([Sales],[Cost of Sales],[Labour Costs],[Overheads])) AS PVTtable

-

 GrpID |  Description  | CustomerID |    Sales   | Cost of Sales | Labour Costs | Overheads
 ------+---------------+------------+------------+---------------+--------------+-----------
    1  |    Sales      |      2     | -178796.18 |     NULL      |      NULL    |     NULL
    2  |COS Description|      2     |    NULL    |   55950.17    |      NULL    |     NULL
    3  |    Labour     |      2     |    NULL    |     NULL      |   18596.43   |     NULL
    4  |   Overheads   |      2     |    NULL    |     NULL      |      NULL    |   47276.48

理想情况下，我希望每个客户输出一行，如下所示:

CustomerID |    Sales   |  Cost of Sales | Labour Costs | Overheads
-----------+------------+----------------+--------------+------------
     2     | -178796.18 |     55950.17   |   18596.43   |   47276.48

Answer 1

任何可用的列都会传递给 PIVOT 函数，因此除了聚合列和透视列之外的所有列都隐式分组依据，因此由于 GrpID 和Description 存在，但不包含它的分组依据，因此每个这些组合您都会得到一行。您需要使用子查询来限制传递给数据透视函数的列:

SELECT  pvt.CustomerID,
        pvt.Sales,
        pvt.[Cost of Sales],
        pvt.[Labour Costs],
        pvt.[Overheads]
FROM    (   SELECT  CustomerID, nominalgroupname, Value
            FROM    trialbalancegrouping
        ) AS t
        PIVOT
        (   SUM(Value)
            FOR nominalgroupname IN 
                (   [Sales],[Cost of Sales],
                    [Labour Costs],[Overheads]
                )
        ) AS pvt;