TSQL Multiple Sums in Pivot

Question

I'd like to sum multiple columns in a pivot statement:
This works correctly for the PhaseId /Unit sum - but I'd also like a PhaseId/Amount Sum.

How would this be accomplished ?

create table temp
(
id int,
teamid int,
userid int,
elementid int,
phaseid int,
units decimal(10, 5),
Amount decimal(10, 5)
)

insert into temp values (1,1,1,3,5,6.74,2.5)
insert into temp values (2,1,1,3,6,8.25,3.25)
insert into temp values (3,1,1,4,1,2.23,1.2)
insert into temp values (4,1,1,4,5,6.8,5.7)
insert into temp values (5,1,1,4,6,1.5,4.2)

select elementid
, [1] as phaseid1
, [5] as phaseid5
, [6] as phaseid6
from
(
select elementid, phaseid, Units, amount
from temp
) x
pivot
(
sum(units)
for phaseid in([1], [5], [6])
) p

Thank you,

Answer 1

Hi @JM12377

This works correctly for the PhaseId /Unit sum - but I'd also like a PhaseId/Amount Sum.

When using PIVOT, we can only aggregate one column at a time. If you want to SUM multiple columns, you could use UNPIVOT or CROSS APPLY to integrate the columns that you want to SUM into the same columns, and add a new column to identify these datas.
For example, from this to this
Then you could do PIVOT based on this modified dataset.
Since you didn't post the result you want, here are two possible queries you may refer to:

--query1: Using CROSS APPLY  
SELECT elementid,Column_Name,[1] AS phaseid1,[5] AS phaseid5,[6] AS phaseid6  
FROM   
(  
 SELECT elementid,phaseid,C.*  
 FROM temp CROSS APPLY(VALUES(Units,'Units'),(amount,'Amount'))C(Column_Value,Column_Name)   
)x  
PIVOT (SUM(Column_Value) FOR phaseid in([1], [5], [6])) p  
ORDER BY elementid  

Output1:

--query2: Using UNPIVOT  
SELECT * FROM   
(  
 SELECT elementid  
       ,'Phaseid'+CAST(phaseid AS VARCHAR(20))+'_'+Column_Name AS Column_Name  
       ,C.Column_Value  
 FROM temp   
 UNPIVOT(Column_Value FOR Column_Name IN(Units,amount))C  
)x  
PIVOT (SUM(Column_Value) FOR Column_Name in([Phaseid1_units],[Phaseid1_Amount],[Phaseid5_units],[Phaseid5_Amount],[Phaseid6_units],[Phaseid6_Amount])) p  
ORDER BY elementid  

Output2:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It's quite simple as long as you stay away from the PIVOT key word, and instead use the standard construction with SUM + CASE, which is a lot more flexible.

   SELECT elementid,  
          SUM(CASE phaseid WHEN 1 THEN units END)  AS units1,  
          SUM(CASE phaseid WHEN 1 THEN Amount END) AS amount1,  
          SUM(CASE phaseid WHEN 5 THEN units END)  AS units5,  
          SUM(CASE phaseid WHEN 5 THEN Amount END) AS amount5,  
          SUM(CASE phaseid WHEN 6 THEN units END)  AS units6,  
          SUM(CASE phaseid WHEN 6 THEN Amount END) AS amount6  
   FROM   temp   
   GROUP  BY elementid  

Answer 3

Maybe a long query will work:

select p.elementid,  
    p.[1] as phaseid1,  
    p.[5] as phaseid5,  
    p.[6] as phaseid6,  
    t.amount1,  
    t.amount5,  
    t.amount6  
from  
(  
    select elementid, phaseid, Units  
    from temp  
) x  
pivot  
(  
    sum(units) for phaseid in([1], [5], [6])  
) p  
inner join   
(  
    select p.elementid,  
        [1] as amount1,  
        [5] as amount5,  
        [6] as amount6  
    from  
    (  
        select elementid, phaseid, Amount  
        from temp  
    ) x  
    pivot  
    (  
        sum(Amount) for phaseid in([1], [5], [6])  
    ) p   
) t on t.elementid = p.elementid