SQL: How to group records, when they have different types

I do not know if I might be over-thinking this task. But I know I need some help. I have to modify a query in order to combine/sum those records that have multiple payment types for the same TransConfirmID.

The data below shows the first three records with the same TransConfirmID, but 2 different payment types. These records should have their payment amount summed up, and the payment type should be 'MULTI'.

The other records are various of the same TransConfirmID but with the same Payment Type for all 'ACH', these records do not need to be summed up/combined, they can stay separated, they do not need to be grouped.

Is this a change I can get help to complete?

I am including a bit of sample data, and the query as I have it so far.

Data

AcctNumber	PaymentAmount	InitiationDate	SourceType	InvoiceNumber	PaymentType	TransConfirmID
80924401229	-41.09			12/17/2019		PRPONL		NULL			CASH		1072667
80924401229	1600			12/17/2019		PRPONL		NULL			CASH		1072667
80924401229	2400			12/17/2019		PRPONL		NULL			ACH			1072667
70919228936	32.39			12/12/2019		PRPKUB		NULL			ACH			34676067987
70919228093	2086.69			12/12/2019		PRPKUB		NULL			ACH			34676067987
70826415014	4003.87			12/13/2019		PRPKUB		NULL			ACH			34728080729
70824306182	8819.61			12/13/2019		PRPKUB		NULL			ACH			34728080729
70913350587	259.54			12/13/2019		PRPKUB		NULL			ACH			34733928441
70913349150	5952.72			12/13/2019		PRPKUB		NULL			ACH			34733928441
70922346155	259.54			12/13/2019		PRPKUB		NULL			ACH			34737135402
70922345404	9225.62			12/13/2019		PRPKUB		NULL			ACH			34737135402

SQL Query

SELECT 
	RIGHT('0000000000000000' + AccountNumber, 16) AS AcctNumber
,	PaymentAmount
,	RIGHT('0' + RTRIM(DATEPART(MM, InitiationDate)), 2) + '/' + RIGHT('0' + RTRIM(DATEPART(dd, InitiationDate)), 2) + '/' + RIGHT(DATEPART(yyyy, InitiationDate), 4) AS InitiationDate
,	PropertyTaxHeader.SourceType AS SourceType
,	InvoiceNumber
,	LTRIM(RTRIM(PaymentType)) AS PaymentType
,	TransConfirmID
FROM 
	PropertyTaxDetail
INNER JOIN PropertyTaxHeader
ON PropertyTaxDetail.HeaderID = PropertyTaxHeader.id
WHERE 	
	PropertyTaxHeader.STATUS = 'Active'
	PaymentStatus NOT IN ('RETN', 'RFND', 'RETURNED', 'REFUNDED') 	
ORDER BY TransConfirmID

DESIRED OUTPUT (based on the sample data, this affects only the 3 first rows really)

AcctNumber	PaymentAmount	InitiationDate	SourceType	InvoiceNumber	PaymentType	TransConfirmID
80924401229 3958.91			12/17/2019		PRPONL		NULL			MULTI		1072667
70919228936	32.39			12/12/2019		PRPKUB		NULL			ACH			34676067987
70919228093	2086.69			12/12/2019		PRPKUB		NULL			ACH			34676067987
70826415014	4003.87			12/13/2019		PRPKUB		NULL			ACH			34728080729
70824306182	8819.61			12/13/2019		PRPKUB		NULL			ACH			34728080729
70913350587	259.54			12/13/2019		PRPKUB		NULL			ACH			34733928441
70913349150	5952.72			12/13/2019		PRPKUB		NULL			ACH			34733928441
70922346155	259.54			12/13/2019		PRPKUB		NULL			ACH			34737135402
70922345404	9225.62			12/13/2019		PRPKUB		NULL			ACH			34737135402

Thank you in advance for your help, and/or for reading this question.

Best regards.

>The data below shows the first three records with the same TransConfirmID, but 2 different payment types. These records should have their payment amount summed up, and the payment type should be 'MULTI'.

If you GROUP BY TransConfirmID then you could say in the SELECT area:

CASE WHEN MIN(PaymentType) <> MAX(PaymentType) THEN 'MULTI' ELSE MIN(PaymentType) END as PaymentType

If PaymentType will ever be null, consider how you want to handle those. This code ignores NULLs, when working out if a MULTI should be shown. If the values are only "CASH/NULL" and this should be a multi, use COALESCE to give the null a value that will never appear in the column:

CASE WHEN MIN(COALESCE(PaymentType, 'xxx')) <> MAX(PaymentType) THEN 'MULTI' ELSE MIN(PaymentType) END as PaymentType

>The other records are various of the same TransConfirmID but with the same Payment Type for all 'ACH', these records do not need to be summed up/combined, they can stay separated, they do not need to be grouped.

You've used language like "can" and "not need" rather than a hard rule of "must/shouldn't" so I assume that grouping by only TransConfirmID is acceptable, because if you do so then these things WILL become combined; you imply that this is OK. If you want them separated for sure, it looks like you should consider grouping by AcctNumberinstead

Alternatively, to answer the question asked in the title - "How to group records, when they have different types", you can use a CASE WHEN do convert the different types into the same thing. Here is an example:

  SELECT
    Country,
    CASE PaymentType 
      WHEN 'CASH' THEN 'Untraceable'
      WHEN 'Bitcoin' THEN 'Untraceable'
      WHEN 'Visa' THEN 'Traceable'
      WHEN 'Mastercard' THEN 'Traceable'
    END as Traceability,
    SUM(Amount) as SumAmount
  FROM
    data
  GROUP BY
    Country,
    CASE PaymentType 
      WHEN 'CASH' THEN 'Untraceable'
      WHEN 'Bitcoin' THEN 'Untraceable'
      WHEN 'Visa' THEN 'Traceable'
      WHEN 'Mastercard' THEN 'Traceable'
    END

Rather than getting sum totals for cash/bitcoin/visa/mastercard (4 rows) this query produces 2 rows, with different values for payment types rolled up into a single value

If you have a lot of values to roll up consider adding a table that maps the hundreds of values for paymenttype to the various (same) traceability values and join it in, then group on the table columns of the joined table