SQL Server Converting Rows to Columns

Question

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

Current Result:

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:

Giving source data (or junk versions) rather than simple pictures (they do help, too), whatever helps us repeat your problem helps tremendously. Otherwise, a bit if guesswork occurs if at all. — clifton_h, Jul 12 '16 at 04:56
@Prdp You should have looked at the current result and desired output before marking question as duplicate. The title of the question is not correct - it has nothing to do with Pivot, so it should not be closed as duplicate — dotnetom, Jul 12 '16 at 04:57
@dotnetom - Please check the current result and expected result once more it requires `Pivot`. I did check before marking it as duplicate ;) — Pரதீப், Jul 12 '16 at 05:12
@Prdp No, it does not require a Pivot, you can perfectly solve this with grouping. Remove duplicate and I will post the answer without a Pivot. — dotnetom, Jul 12 '16 at 05:16
@dotnetom - Done. And don't post conditional Aggregate answer ;) — Pரதீப், Jul 12 '16 at 05:19
This is still pretty much a duplicate of the fifteen billion other questions that require pivots/case aggregation... — ZLK, Jul 12 '16 at 05:21

score 0 · Accepted Answer · answered Jul 12 '16 at 05:20

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

Did you read my first comment. The duplicate I suggested has similar answer. This is a duplicate question — Pரதீப், Jul 12 '16 at 05:24
@Prdp The duplicate that you suggested has `PIVOT` related accepted answer, and one answer that suggested subqueries as a solution, however none of the answers suggested conditional aggregates. I agree that there might be dozens of duplicate answers similar to the one I provided, but it is definitely not duplicate of the one you suggested. — dotnetom, Jul 12 '16 at 05:30

SQL Server Converting Rows to Columns

1 Answers1

Linked