GROUP BY and create columns for each unique value grouped on (output as 1 row always), then use the output as a JOIN - SQL Server

Question

I need to GROUP data BY 2 columns (BillingId and PaymentType) - no issues with that AND have output of 1 row with columns of unique PaymentType - issue with this step. So each BillingId has only 1 row and that table will be used for joining another Tables in the Database.

Billing Table:

BillingId (unique)

 12345
 67890

Payment Table:

PaymentId PaymentType  BillingId PaymentAmount
 (unique)

   12      electronic    12345      62.29
   14      electronic    12345      73.28
   56      electronic    12345     -62.29
   6       electronic    67890      83.58
   2       adjustment    67890      30.43

MY CODE:

SELECT GroupedTable.* 

FROM (SELECT b.BillingId, 
             p.PaymentType, 
             SUM(p.PaymentAmount) AS AmountPaid
      FROM Billing AS b
      LEFT JOIN Payment AS p
        ON (b.BillingId = p.BillingId)
      GROUP BY b.BillingId, p.PaymentType) AS GroupedTable

OUTPUT (obviously incorrect):

  BillingId  PaymentType   AmountPaid

     67890    electronic    83.58
     12345    electronic    73.28
     67890    adjustment    30.43

OUTPUT I NEED:

BillingId    AmountPaid     AmountAdjusted 
            (electronic)     (adjustment)

  67890         83.58           30.43
  12345         73.28            0

Anton Grig · Answer 1 · 2022-01-21T19:55:30.820

It looks easier if you use Case When expressions as follows:

Select B.BillingId, Sum(Case When P.PaymentType='electronic' Then P.PaymentAmount End) As [AmountPaid (electronic)],
                    Sum(Case When P.PaymentType='adjustment' Then P.PaymentAmount End) As [AmountAdjusted (adjustment)] 
From Billing As B Left Join Payment As P On (B.BillingId=P.BillingId)
Group by B.BillingId

db<>fiddle

BillingId	AmountPaid (electronic)	AmountAdjusted (adjustment)
12345	73,28	NULL
67890	83,58	30,43

score 3 · Accepted Answer · answered Jan 21 '22 at 19:49

You should group by BillingId only and use conditional aggregation:

SELECT b.BillingId, 
       SUM(CASE WHEN p.PaymentType = 'electronic' THEN p.PaymentAmount ELSE 0 END) AS AmountPaid,
       SUM(CASE WHEN p.PaymentType = 'adjustment' THEN p.PaymentAmount ELSE 0 END) AS AmountAdjusted
FROM Billing AS b LEFT JOIN Payment AS p
ON b.BillingId = p.BillingId
GROUP BY b.BillingId;

See the demo.

GROUP BY and create columns for each unique value grouped on (output as 1 row always), then use the output as a JOIN - SQL Server

2 Answers2