Pivoting two columns with sum

Question

This is my sample Table

**BranchId   GroupCode  Cash    Credit**
1000                AA  10      8644
1000                AA  12      1244
1000                BB  20      7535
1000                CC  30      5633
1001                AA  50      5763
1001                AA  34      2343
1001                BB  60      1000
1001                BB  62      2346
1002                BB  34      1600
1002                CC  68      1700

I want the sample output in this form as shown in the below

**BranchId | AA_Cash | AA_Credit | BB_Cash | BB_Credit | CC_Cash | CC_Credit**
1000         ?                      
1001                        
1002

? = I need sum of Cash and Credit in each branches

select * from
(select bid, GroupCode, Cash FROM dueList) as T 
PIVOT (sum(Cash) for GroupCode in([AA_Cash],[BB_Cash],[CC_Cash])) PT

This SQL is give output but only cash column, I need add credit column set to the output.

I try using following link

In Sql Server how to Pivot for multiple columns

but in my database there are only 16 branches. Once I try samples in above link it display duplicate lines for branches and lots of null in number area.

score 1 · Answer 1 · answered May 22 '19 at 13:58

You can PIVOT your multi-column data by UNPIVOTing first

For a better visual, run the SELECT within the src subquery

Example

Select *
 From  (
        Select A.BranchID
         From  YourTable A
         Cross Apply ( values (GroupCode+'_Cash'  ,Cash)
                             ,(GroupCode+'_Credit',Credit)
                     ) B(Item,Value)
       ) src
 Pivot (sum(Value) for Item in ([AA_Cash],[AA_Credit]
                               ,[BB_Cash],[BB_Credit]
                               ,[CC_Cash],[CC_Credit]
                               ) )pvt

EDIT

On a side-note, it is important to "FEED" your pivot with only the required columns. In your posted example, you included GroupCode. This would generate additional records.

score 0 · Answer 2 · answered May 22 '19 at 14:22

Just use conditional aggregation!

select BranchId,
       sum(case when groupcode = 'AA' then cash end) as aa_cash,
       sum(case when groupcode = 'AA' then credit end) as aa_credit,
       sum(case when groupcode = 'BB' then cash end) as bb_cash,
       sum(case when groupcode = 'BB' then credit end) as bb_credit,
       sum(case when groupcode = 'CC' then cash end) as cc_cash,
       sum(case when groupcode = 'CC' then credit end) as cc_credit
from t
group by BranchId;

Pivoting two columns with sum

2 Answers2