SQL - Multiple Aggregations on Pivot Table-CodePudding

I have a table of data which is originally displayed like this:

I have created a pivot query:

SELECT  Ref,
        COALESCE([SALES],'N/A') AS [SALES],
        COALESCE([PURCHASING], 'N/A') AS [PURCHASING]

FROM

(SELECT Ref,
        Sub_Ref,
        Division

FROM dbo.Accounts_Download

WHERE Ref = 'MCA042G21AA'

) AS SourceTable

Pivot (MAX(Sub_Ref) For Division in ([SALES],[PURCHASING])) AS CvgPivot

to display the data like this:

However, I have been asked to add in a new field called Profit:

It doesn't seem to matter where I insert the SUM function in my pivot query. I keep coming back to the data being displayed like this:

Any help on how to use multiple aggregations in one pivot query would be much appreciated. As everything I'm doing is coming back to multiple rows. I'd really like the data to be displayed like this:

Thanks in advance

CodePudding user response：

This will work for you

     select ref,min(sales)SALES,min(purchasing) PURCHASING,sum(profit) profit from (
    SELECT  Ref,
    COALESCE([SALES],'N/A') AS [SALES],
    COALESCE([PURCHASING], 'N/A') AS [PURCHASING],profit

    FROM

    (SELECT Ref,
    Sub_Ref,
    Division,
    profit
    FROM Accounts_Download

    WHERE Ref = 'MCA042G21AA'

    ) AS SourceTable

    Pivot (MAX(Sub_Ref)  
    For Division in ([SALES],[PURCHASING])) AS CvgPivot) as a
    group by ref

check the link https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=43951fbc18c43e502f12d1612cac64e3

CodePudding user response：

Also it could be done like this:

;WITH SourceTable 
AS (
    SELECT Ref,
    Sub_Ref,
    Division,
    Profit
    FROM Accounts_Download
    WHERE Ref = 'MCA042G21AA'
),

cte AS (
    SELECT  Ref, 
            Sub_ref, 
            Division, 
            SUM( Profit) OVER ( PARTITION BY Ref ORDER BY Ref, Sub_ref ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Profit 
    FROM SourceTable
)

SELECT  Ref, 
        COALESCE( Sales, 'N/A') Sales, 
        COALESCE( Purchasing, 'N/A') Purchasing, 
        Profit 
FROM cte
PIVOT (
    MAX( Sub_ref) 
    FOR Division IN ( [SALES], [PURCHASING])
) AS CvgPivot

Basically, PIVOT groups your data by selected columns which are not presented in PIVOT i.e. Ref, Profit