数据透视表返回多行 NULL，结果应分组在一行上

2022-01-22 00:00:00 pivot sql group-by sql-server

我有下面的表格，我希望将其作为数据透视表，以便第 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;

相关文章