Pivot table returns multiple rows with NULL, resul

问题:

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

回答1:

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;