I'm trying to select this results of multiple rows into a single row and grouping/merging them by DocNumber.

I have a table like this

create table PaymentsTable (
    PaymentId varchar(10),
    DocNumber varchar(20),
    DocType varchar(40),
    Amount decimal(18,2)
);

insert into paymentstable values (01,2020, 'receipt', 100);
insert into paymentstable values (02,2020, 'receipt',150);
insert into paymentstable values (03,2020, 'receipt',10);
insert into paymentstable values (01,600, 'order', 1500);
insert into paymentstable values (01,220, 'invoice', 650);
insert into paymentstable values (02,220, 'invoice',500);

The table looks like this

I'm trying something like this

SELECT P.docnumber
    , p.doctype
    , CASE WHEN P.Paymentid = 01 THEN p.Amount END AS 'Cash'
    , CASE WHEN P.Paymentid = 02 THEN p.Amount END AS 'Debit'
    , CASE WHEN P.Paymentid = 03 THEN p.Amount END AS 'Credit'
FROM PaymentsTable AS P

My Result:

Desired:

CodePudding user response：

Seems you just need GROUP BY and SUM?

SELECT P.docnumber
    , p.doctype
    , SUM(CASE WHEN P.Paymentid = 01 THEN p.Amount END) AS 'Cash'
    , SUM(CASE WHEN P.Paymentid = 02 THEN p.Amount END) AS 'Debit'
    , SUM(CASE WHEN P.Paymentid = 03 THEN p.Amount END) AS 'Credit'
FROM PaymentsTable AS P
GROUP BY P.docnumber, p.doctype

CodePudding user response：

SELECT DocNumber,DocType,
CASE 
WHEN SUM(Cash) IS NULL THEN 0.00
ELSE ROUND(SUM(Cash),2) END AS Cash,
CASE 
WHEN SUM(Debit) IS NULL THEN 0.00
ELSE ROUND(SUM(Debit),2) END AS Debit,
CASE 
WHEN SUM(Credit) IS NULL THEN 0.00
ELSE ROUND(SUM(Credit),2) END AS Credit
FROM 
(SELECT P.docnumber
    , p.doctype
    , CASE WHEN P.Paymentid = 01 THEN p.Amount END AS 'Cash'
    , CASE WHEN P.Paymentid = 02 THEN p.Amount END AS 'Debit'
    , CASE WHEN P.Paymentid = 03 THEN p.Amount END AS 'Credit'
FROM PaymentsTable AS P) table1
    GROUP BY DocNumber,DocType
    ORDER BY docnumber DESC;