数据透视表返回多行为NULL，结果应分组为一行 [英] Pivot table returns multiple rows with NULL, results should be grouped on one row

问题描述

我有一个要查找的数据透视表，以便第1列中的描述成为新数据透视表中的列标题.

I have the table below which I am looking to pivot so that the descriptions in column 1 become column headers in the new pivot.

 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

我正在使用下面的代码来获得下面的结果集:

I'm using the code below to get the result set below that:

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

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

Ideally, I'd want the output to be one row per customer, like this:

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

推荐答案

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

Any columns that are available are passed to the PIVOT function, so all apart from the column aggregated, and the column pivoted are implicitly grouped by, so since GrpID and Description are present, and not included it is grouped by, therefore you get one row per combination of these. You need to limit the columns passed to the pivot function by using a subquery:

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;

这篇关于数据透视表返回多行为NULL，结果应分组为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！