Vicky Vicky - 4 months ago 13
SQL Question

SQL Server Converting Rows to Columns

I am currently extracting data using 3 different tables, and below is the output.

Current Result:
enter image description here

Query Used:

SELECT
dbo.TableB.TrackingID, dbo.TableA.FinancialID,
dbo.TableA.ParcelCode, dbo.TableA.TotalAmount,
dbo.TableB.FinanceType, dbo.TableB.TransactionType,
dbo.TableC.CustID
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')


I would like to have the following output:

Desired Output:
enter image description here

Answer

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