You can get the output you desire with grouping and some CASE
statements inside SUM
aggregate functions:
SELECT
dbo.TableB.TrackingID,
dbo.TableA.ParcelCode,
dbo.TableC.CustID,
SUM(CASE WHEN dbo.TableB.FinanceType="Invoice" THEN dbo.TableA.TotalAmount ELSE 0 END) AS TotalAmount,
SUM(CASE WHEN dbo.TableB.FinanceType="Invoice" AND TransType="Card" THEN dbo.TableA.TotalAmount ELSE 0 END) AS CardInvoice,
SUM(CASE WHEN dbo.TableB.FinanceType="Invoice" AND TransType="Cash" THEN dbo.TableA.TotalAmount ELSE 0 END) AS CashInvoice,
SUM(CASE WHEN dbo.TableB.FinanceType="PaymentRecepit" THEN dbo.TableA.TotalAmount ELSE 0 END) AS CardPaymentRecepit
FROM dbo.TableA
INNER JOIN dbo.TableB ON dbo.TableA.FinancialID = dbo.TableB.FinancialID
INNER JOIN dbo.TableC ON dbo.TableB.TrackingID = dbo.TableC.TrackingID
WHERE
dbo.TableB.TrackingID = '17006218AU'
GROUP BY
dbo.TableB.TrackingID,
dbo.TableA.ParcelCode,
dbo.TableC.CustID
3
solved SQL Server Converting Rows to Columns